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.
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
vResult
Question.... If i was to add more Actuals for 2023, and more budgets for 2024, 2025 etc... would they filter through the slicers on the card as well??? I'm not sure if i need to extend the DAX to include columns [Year] and have 2022, 2023 etc.
Would i be better off Concatenating my data in excel to "2022 Actuals" "2023 Actuals" "2023 Budget" "2024 Budget" Etc.. and then have multiple cards for variances?
Ideally i want one card but i don't think its possible if i don't alter the data as the slicers can only do so much and it obviously won't slice the Dax to what i want compared.
I think a card for each variance is needed?
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.