Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
104 | |
77 | |
68 | |
61 |
User | Count |
---|---|
148 | |
107 | |
106 | |
82 | |
70 |