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
How do i calculate the variance between 2 matrix columns based on slicer selections?
-
aufordhamst
- Posts: 1
- Joined: Mon May 05, 2014 4:09 pm
-
MattAllington
- Posts: 1154
- 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?
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)
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
Power BI Training
Return to “Power BI Desktop/Service”
Who is online
Users browsing this forum: No registered users and 0 guests


