How do i calculate the variance between 2 matrix columns based on slicer selections?

Used for anything related to the new Power BI Desktop and Service tools
aufordhamst
Posts: 1
Joined: Mon May 05, 2014 4:09 pm

How do i calculate the variance between 2 matrix columns based on slicer selections?

Postby aufordhamst » Fri Dec 18, 2020 12:01 pm

Outline
I have a large table with multiple financial fields for many products and 'scenarios'.
I use a slicer to select the product and the 2 'scenarios' that i want to review.
I specifically want to calculate the variance between the 2 columns that appear in the table.
I could create Measures but I have many scenarios that do not follow a naming convention. This approach would require creating a measure for each product/scenario/financial field - not such a good approach.
Is there a way in which I can create a 'dynamic' measure that uses the selected slicer 'scenarios'?

Example
Slicer for Products has "Product A" selected
Slicer for Scenarios contains "Scenario A", "Scenario B", "Scenario C", "Scenario D", "Scenario E"
The 2 selections in the slicer are "Scenario A" & "Scenario E"
The matrix is as follows:
Fields Scenario A Scenario E Variance
Revenue 100 125 25
Costs -60 -65 -5
Profit 40 60 20

How do I calculate the Variance column?
Many thanks

MattAllington
Posts: 1160
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: How do i calculate the variance between 2 matrix columns based on slicer selections?

Postby MattAllington » Sun Dec 20, 2020 7:59 am

I assume you have a column for scenario. I would create 2 disconnected tables of the scenario names. Write 2 measures that respond to these selections (one to each). Something like this

VAR selectedScenario = selectedvalue(disconnectedTable1[scenario])
Return CALCULATE(Base measure],datatable[scenario] = selectedScenario)
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Akusta
Posts: 1
Joined: Sat Sep 11, 2021 1:16 am

Re: How do i calculate the variance between 2 matrix columns based on slicer selections?

Postby Akusta » Mon Sep 13, 2021 4:25 am

I believe you will need to use DAX here as that is outside the bounds of native Power BI. It also depends upon the actual visual you use. A Matrix operates very differently then a table with regards to column names and/or potential dynamic features.


Return to “Power BI Desktop/Service”

Who is online

Users browsing this forum: No registered users and 1 guest

cron