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.
When Actuals and Budget have the same grain, I prefer to create separate columns for Actuals and Budget. This reduces table size, simplifies DAX, and improves performance.
To compare Actuals and Budget for different years, you'll need two Year slicers (one for Actuals, and one for Budget). These Year slicers will need to use disconnected tables (no relationship to the fact table) because filtering on a particular year in the date table would exclude rows for the other year. You can use DAX to filter for the Year in each slicer.
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.