Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
I have my data laid out in the following format:
There are 2 years of data - 2022 Actuals and 2023 Budget.
How would i get a Card Visual to display the variance for Actuals V Budget even though they are in the same column?
Below is how the layout of the dashboard is. I have slicers for each column and ideally if i filter by for example "Period 1" i would like the variance to show just period 1 also, aswell as fleet? so it works with the slicers?
Many thanks!
Solved! Go to Solution.
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
Proud to be a Super User!
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.
Proud to be a Super User!
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
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Proud to be a Super User!
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?
What are the 4 columns? I noticed that your table name contains the year. It's better to have generic table names and use columns to distinguish the data. Otherwise, you might have another table '23 Actuals', '24 Actuals', etc. These can be appended into one master table, and use the Year (and Reporting Year) column to distinguish the data.
Proud to be a Super User!
Hi @DataInsights
You really are a super user! Thanks so much.... saved me alot of headaches!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
82 | |
65 | |
63 | |
57 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |