Forum Discussion
Adding a variance column to matrix table when connected to a live dataset
Hello,
There is a live dataset (maintained by a different team) that i am connecting to via SSAS service.
I have an actuals columns which is split into two columns based on the time periods I select. I could either select any two different months, two quarters or two years to compare. I'd like to add a variance column to the right of this to make the comparison. So my matrix visual would look like the below with Q1 and Q2 representing the actual spend in each quarter and the light orange variance column being the desired column I want to add showing the different between the two columns.
Just as a reminder, i am connecting to a live dataset - not extracting and transforming data from excel extracts.
Thanks in advance for any help / guidance receieved.
Create a measure that calculates the individual values for the cells but calculates the difference between the value for the min time period and the value for the max time period for the column totals (using HASONEVALUE to tell). You can even rename the string "Total" to "Variance" for the column totals.
3 Replies
- lbendlin
Super User
Create a measure that calculates the individual values for the cells but calculates the difference between the value for the min time period and the value for the max time period for the column totals (using HASONEVALUE to tell). You can even rename the string "Total" to "Variance" for the column totals.
Could you elaborate more on the measure? I can't figure out how to use HASONEVALUE to select the right time period.
- lbendlin
IF(HASONEVALUE() resolves to TRUEif you are in an individual row, and most of the time resolves to FALSE if you are in the Totals area (unless you only have one row in the visual to begin with)
IF(HASONEVALUE(<row field>),<item measure>,<totals measure>)
