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.
Hey Friends,
I've got request from a user that I can't seem to fulfill correctly:
I've got a data set that looks like:
They've asked for some "Matrix" to add to the visuals. The first is a dynamic matrix that includes sales. The Matrix works great for this. I created a hierarch with "Brand" (A,B,C) and beneath it is the Product Line (F or M).
The Problem is that they have now asked to have all the values replaced with a percent of total. I wrote the following DAX measure:
% of Total =
DIVIDE (
SUM ( Data[ Sales ] ),
CALCULATE (
SUM ( Data[ Sales ] ),
ALL ( Data[Brand],Data[Product Line] )
),
0
)
When I use this measure, it totals the columns to 100, but I need the rows to add up to 100.
I think it's because it's Friday afternoon, but I can't for the life of me get this to work....
Solved! Go to Solution.
Hi @Teige,
Based on my test, here we just need to update your formula as below, then we can get the result as we excepted.
% of Total1 = DIVIDE ( SUM ( Data[Sales] ), CALCULATE ( SUM ( Data[Sales] ), ALLEXCEPT(Data,Data[Brand]) ))
For more details, please check the pbix as attached.
https://www.dropbox.com/s/cf2ri8l4e7rlt9x/Dynamic%20Percent%20of%20Total%20in%20a%20Matrix.pbix?dl=0
Regards,
Frank
Hi @Teige,
Based on my test, here we just need to update your formula as below, then we can get the result as we excepted.
% of Total1 = DIVIDE ( SUM ( Data[Sales] ), CALCULATE ( SUM ( Data[Sales] ), ALLEXCEPT(Data,Data[Brand]) ))
For more details, please check the pbix as attached.
https://www.dropbox.com/s/cf2ri8l4e7rlt9x/Dynamic%20Percent%20of%20Total%20in%20a%20Matrix.pbix?dl=0
Regards,
Frank
Thanks! Yes! Allexcept!
Thanks so 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 |