Forum Discussion
Get Variance from the same column
Try this measure:
Variance = VAR vActuals = CALCULATE ( SUM ( Table1[Value] ), Table1[Actuals/Budget] = "Actuals" ) VAR vBudget = CALCULATE ( SUM ( Table1[Value] ), Table1[Actuals/Budget] = "Budget" ) VAR vResult = vBudget - vActuals RETURN vResultSince you are trying to slice by different years simultaneously, you'll have to create separate Year tables to use for slicers. Alternatively, you could create a column Reporting Year and populate it with 2022 for the "2022 Actuals" and "2023 Budget" rows. Then you could slice by Reporting Year.
Makes sense!
My issue is my layout in excel. I have this layout below,
I have then unpivoted columns F-R, which then looks like this below. I don't really know how else to do it. unless i create different sheets in excel for each years actuals V Budget and upload them to PBI as different queries?
After you unpivot columns F-R, pivot column Actuals/Budget:
This will create separate columns for Actuals and Budget.
This looks good,
But then they are 2 seperate entities which i can not slice in my matrix table.. it just makes 2 columns per "Period"Since you are trying to slice by different years simultaneously, you'll have to create separate Year tables to use for slicers. Alternatively, you could create a column Reporting Year and populate it with 2022 for the "2022 Actuals" and "2023 Budget" rows. Then you could slice by Reporting Year.
Yes that makes sense.
I will make 2 columns. actuals/ budget then year and slice them both to what i want to see like below.Finally: If i wanted to add more measures in to this DAX below, to include "2022" from [Year] Column, "Actuals" from [Actuals/Budget] Column, to then less "2023" from [Year] Column, "Budget" from Budget column. How would i do it?
Same answer as the one below but from 4 columns rather than 2?22 v 23 Variance =VAR vActuals =CALCULATE ( SUM ( '22 Actuals'[Value] ), '22 Actuals'[Actuals/Budget] = "2022 Actuals" )VAR vBudget =CALCULATE ( SUM ( '22 Actuals'[Value] ), '22 Actuals'[Actuals/Budget] = "2023 Budget" )VAR vResult = vBudget - vActualsRETURNvResultThanks!