Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
How can I create a third column in a matrix to show variance YOY, without creating individual measures for reach value of Revenue, Costs, Profit, etc.?
2018 | 2019 | third column for VAR | |
Revenue | 1000000 | 1200000 | =2019 Rev / 2018 Rev - 1 |
Costs | 200000 | 200000 | etc |
Profit | 800000 | 1000000 |
Cheers,
Hi, @Anonymous
Here ,we can use a measure as below to work around:
Measure =
var sum18 = CALCULATE(SUM('Table'[values]),FILTER('Table', 'Table'[Year] =2018))
var sum19 = CALCULATE(SUM('Table'[values]),FILTER('Table','Table'[Year]=2019))
return
IF(ISINSCOPE('Table'[Year]),SUM('Table'[values]),sum19/sum18-1)
Besides, you can change the name of “subtotals label” from “Total” into “third column for Var” in “format”.
Here’s a sample I made:
Best Regards,
Community Support Team _ Eason Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-easonf-msft,
This would work... but my data is structured differenty.
In my dataset, I have a column for Revenue, Costs & Profit. So I need the measure to calculate the variance of that row, I cannot specify I column as there are 3 different columns...
= divide(sum(2019)-sum(2018),sum(2018))
or
measure =
var _a =sum(2019)-sum(2018)
return
divide(_a,sum(2018))
Please share your table structure
See below screenshot... will this work? or how else can I share my table structure?
I think one of the obstacles here is that each of the rows in the matrix come from different columns in the data table... which means the DAX formula for the variance cannot reference a specific column, but needs to reference the row it is on.
Cheers,
Dan