Page 1 of 1

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

Posted: Fri Dec 18, 2020 12:01 pm
by aufordhamst
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

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

Posted: Sun Dec 20, 2020 7:59 am
by MattAllington
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)

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

Posted: Mon Sep 13, 2021 4:25 am
by Akusta
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.