Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Guys,
My Data looks like this and I need to show it in a matrix visual with a cumulative sum of values. Please note that there is a slicer filter at the top that switches the column values between Month_num and Month.
Input
Country | Value | Month_num | Month |
US | 100 | 1 | 1/1/2022 |
US | 120 | 2 | 2/1/2022 |
US | 140 | 3 | 3/1/2022 |
US | 110 | 4 | 4/1/2022 |
US | 145 | 5 | 5/1/2022 |
US | 180 | 6 | 6/1/2022 |
US | 170 | 7 | 7/1/2022 |
Germany | 140 | 1 | 4/1/2022 |
Germany | 150 | 2 | 5/1/2022 |
Germany | 160 | 3 | 6/1/2022 |
Germany | 170 | 4 | 7/1/2022 |
Germany | 180 | 5 | 8/1/2022 |
Germany | 130 | 6 | 9/1/2022 |
Germany | 140 | 7 | 10/1/2022 |
Output 1 - Wheb Slicer value is selected as Month
Month | 1/1/2022 | 2/1/2022 | 3/1/2022 | 4/1/2022 | 5/1/2022 | 6/1/2022 | 7/1/2022 | 8/1/2022 | 9/1/2022 | 10/1/2022 |
US | 100 | 220 | 360 | 470 | 615 | 795 | 965 | |||
Germany | 140 | 290 | 450 | 620 | 800 | 930 | 1070 |
Output 2 - When the slicer value is selected as Month_num
Month_num | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
US | 100 | 220 | 360 | 470 | 615 | 795 | 965 |
Germany | 140 | 290 | 450 | 620 | 800 | 930 | 1070 |
Can someone please help me to achieve this?
Thanks,
Anuj
Solved! Go to Solution.
Hi @itsmeanuj
If I understood you correctly and you are working with the field parameters you can apple the following steps :
1. Add to the field parameter column of the slicer's selections ( you will need it at the dax that will switch the formula according to your selection)
2. Use this formula for the calculation of the running total :
4. Create the matrix (parameter should be on columns)
Result:
PBIX is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
@Ritaf1983 - Thanks for your response on this. I realized that in the sample data, you have changed the dates of Germany. US data is from Jan-22 to July 22. Germany is from April 22 to Oct-22. In the actual data, there are a lot more countries with different starting dates. will this solution still work in that scenario?
Hi @itsmeanuj
This logic will work for any date format according to your needs, everything is dynamic in my formulas.
I changed your format just because my settings are dd/mm/yyyy , and I didn't want to reset them just for the sample solution 🙂
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
User | Count |
---|---|
90 | |
73 | |
68 | |
63 | |
56 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |