Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
My dataset looks like this:
I am creating a Matrix to see % of totals. But, the problem is to not be able to calculate 100% for each column. In power BI, it looks this:
But, I would like to see the calculation like this:
Solved! Go to Solution.
Hi @burak. You can get the desired result with the three measures below. Names can be whatever makes sense.
Sum of Units = SUM(Table1[Unit Taken]) Sum of Units All College = CALCULATE([Sum of Units], ALL(Table1[College])) Percent of All = DIVIDE([Sum of Units], [Sum of Units All College])
You could also do it all in one measure, but based on your other posts, it would be good to keep these as building blocks that you can use in other places.
Hi @burak. You can get the desired result with the three measures below. Names can be whatever makes sense.
Sum of Units = SUM(Table1[Unit Taken]) Sum of Units All College = CALCULATE([Sum of Units], ALL(Table1[College])) Percent of All = DIVIDE([Sum of Units], [Sum of Units All College])
You could also do it all in one measure, but based on your other posts, it would be good to keep these as building blocks that you can use in other places.
Thank you for your help. It works, but I have a little concern.
One of the colleges is filtered out in my PBI and this formula still does not show 100%. Although the table does not show the filtered college, it keeps the percentage on the background. As a result, I am getting total percentages in each column like 99%.
Is there any way to correct the formula?
@burak. Change the ALL(Table1[College]) to ALLSELECTED(Table1[College]). That will count the colleges not filtered out by other things.
Great! Thank you very much.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |