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'm straggling with the calculation groups 'cause I'm trying to add three total columns, one for Current Year, one for Last Year and the column with the variation between these two. My table has also months columns because I need to show the evolution of my sales over the last 12 months. I followed the very helpful video https://www.youtube.com/watch?v=90X5_tISxIY and I was able to create the same scenario. The problem comes when I apply TOPN sales feature to filter my data. For the TOPN I have created a dynamic measure that allowes me to calculate the ranking on totals based on the selected measure (as I have also created a parameter field to select 3 different measure). On top of this, I have created a numeric field slicer in order to select the desired TOPN from 1 to 20 and linked this slicer to a measure called "visible rows" that gives me 0 for all sales above the ranking 20 and used this measure in the visualisation filter to visualize only the TOPN selected from the slicer. Until here, everything works perfectly: I see my TOPN customers by month and the totals are matching with the visualisation.
Now, I need to add those three additional columns and to do so I have created a calculation group with three items: CY, LY and CY vs LY %:
Solved! Go to Solution.
Certainly! To adjust your existing measure and handle the scenario where people have more hours remaining than available, you can modify your calculation. Let’s create a new measure that takes into account the capped hours for each period:
Remaining Hours = VAR NovDecCap = MIN(200, [Nov-Dec Cap Hours]) VAR JanOctCap = MIN(200, [Jan-Oct Cap Hours]) VAR TotalCap = 350 VAR UsedHours = NovDecCap + JanOctCap VAR Remaining = TotalCap - UsedHours RETURN IF(Remaining < 0, 0, Remaining)
Explanation:
Apply this new measure to your matrix, and it should handle the scenario correctly. If you have similar measures, you can follow a similar approach.
Feel free to adapt this logic to other measures as needed. Let me know if you need further assistance! 😊
Certainly! To adjust your existing measure and handle the scenario where people have more hours remaining than available, you can modify your calculation. Let’s create a new measure that takes into account the capped hours for each period:
Remaining Hours = VAR NovDecCap = MIN(200, [Nov-Dec Cap Hours]) VAR JanOctCap = MIN(200, [Jan-Oct Cap Hours]) VAR TotalCap = 350 VAR UsedHours = NovDecCap + JanOctCap VAR Remaining = TotalCap - UsedHours RETURN IF(Remaining < 0, 0, Remaining)
Explanation:
Apply this new measure to your matrix, and it should handle the scenario correctly. If you have similar measures, you can follow a similar approach.
Feel free to adapt this logic to other measures as needed. Let me know if you need further assistance! 😊
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |