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,
I currently have a page with several slicers from different tables(they all have relationships).
But I'm trying to find the percentage of Weight this Product represents of the total Weight given all the selected slicers. So I´d need both a Weight affected by every filter. And a Weight that´s affected by every filter besides Product.
For Example this my full table. If I needed what percentage of Green Product C was sold in USA out of the total Weight. I´d need:
-Weight of C (Affected by Product, Color, Country) = 1
-Total Weight (Affected by Color, Country but NOT Product) = 33
Measure Result=1/33
Product Model Color Weight Country
A | 2 | Green | 9 | USA |
C | 3 | Green | 1 | USA |
A | 2 | Red | 6 | MEX |
C | 2 | Green | 1 | MEX |
B | 2 | Green | 8 | USA |
B | 1 | Red | 9 | CAN |
A | 1 | Green | 4 | MEX |
A | 1 | Red | 9 | CAN |
B | 1 | Blue | 7 | MEX |
C | 1 | Red | 9 | CAN |
A | 2 | Green | 10 | USA |
B | 2 | Blue | 5 | MEX |
C | 3 | Blue | 4 | MEX |
C | 1 | Blue | 1 | MEX |
A | 3 | Red | 1 | USA |
B | 2 | Green | 5 | USA |
B | 3 | Green | 10 | MEX |
C | 3 | Blue | 9 | CAN |
C | 2 | Green | 2 | MEX |
B | 3 | Green | 7 | MEX |
Thanks in advance
Solved! Go to Solution.
Hi @SuchCT ,
For this you need to use the ALLEXCEPT using the two columns you need:
Weight for product =
DIVIDE (
SUM ( 'Table'[Weight] ),
CALCULATE (
SUM ( 'Table'[Weight] ),
ALLEXCEPT ( 'Table', 'Table'[Country], 'Table'[Color] )
)
)
Check result below:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @SuchCT ,
For this you need to use the ALLEXCEPT using the two columns you need:
Weight for product =
DIVIDE (
SUM ( 'Table'[Weight] ),
CALCULATE (
SUM ( 'Table'[Weight] ),
ALLEXCEPT ( 'Table', 'Table'[Country], 'Table'[Color] )
)
)
Check result below:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis one sort of works. I can´t really add everything I need because the filters interact with each other, so it's not as easy as too just add them on ALLEXCEPT.
I'll accept it as solution as to help anyone else. Thanks for the help.
Hi @SuchCT ,
What do you mean "sort of works" were is this lacking the flexebility you need?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI think the problem comes from the iflters interacting with each other. I have a lot of slicers on this page. Continuing with the example if I were to filter by "Color" and "Model" on the slicers that would automatically modify the other slicers.
So now, eventhough I added "Color" and "Model" to ALLEXCEPT, the other parameters ("Country" and "Products") are being modified as well.
The workaround I found is to just add to the ALLEXCEPT the Fields that remain constant, the more "general" ones.
Excuse me if I'm not expressing myself clearly, english is not my first language.
Hi @SuchCT ,
This can happen when you use a single table for your data, because the filtering impacts other slicer the option would be to have disconnected tables for the slicer and then make the calculation using the values selected on those slicers but that would also give you some other issues.
(no problem about the english is not mine first language also).
If you need anything else please tell me.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks for your reply, I found that method and am using it right now. But I was looking for a more "hands off" solution. In case in the future someone needs to add a new filter, they don't have to modify the measure.
If that's not possible I'll accept your post as solution. Thanks again.
Hi @SuchCT ,
In this case you need to use the following syntax:
Weight for product =
DIVIDE (
SUM ( 'Table'[Weight] ),
CALCULATE (
SUM ( 'Table'[Weight] ),
ALL ( 'Table'[Product] )
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsTry:
Weight for product =
DIVIDE (
SUM ( 'Table'[Weight] ),
CALCULATE (
SUM ( 'Table'[Weight] ),
ALL ( 'Table', 'Table'[Product] )
)
)
Proud to be a Super User!
Paul on Linkedin.
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 |
---|---|
108 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |