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.
Using dummy data to explain:
Team | Category | Amount |
A | Green | 1 |
A | Blue | 2 |
A | Red | 3 |
A | Total | 6 |
B | Total | 10 |
C | Green | 5 |
C | Blue | 4 |
I need the total amount without the category Red, but not all the teams have all the categories, therefore I need the amount from:
Total - Red
or
Green + Blue
or
Total
in that order or preference.
How can make a filter like that?
Solved! Go to Solution.
I've come up with something like this:
Total = IF ( Calculate (SUM(Table[Amount], Table[Category] = "Total")) - Calculate (SUM(Table[Amount], Table[Category] = "Red")) >0,
Calculate (SUM(Table[Amount], Table[Category] = "Total")) - Calculate (SUM(Table[Amount], Table[Category] = "Red")),
Calculate (SUM(Table[Amount], Table[Category] in {"Green","Blue"})))
And it seems to work in this case... 🙂
@Anonymous
Can you please expand on what you mean by "in that order of preference"?
What is the final visual structure? Can you provide a sample mockup depiction?
Proud to be a Super User!
Paul on Linkedin.
I've come up with something like this:
Total = IF ( Calculate (SUM(Table[Amount], Table[Category] = "Total")) - Calculate (SUM(Table[Amount], Table[Category] = "Red")) >0,
Calculate (SUM(Table[Amount], Table[Category] = "Total")) - Calculate (SUM(Table[Amount], Table[Category] = "Red")),
Calculate (SUM(Table[Amount], Table[Category] in {"Green","Blue"})))
And it seems to work in this case... 🙂
@Anonymous , You need to create an independent table with these values and based on the selection you need to create a measure
same as measure slicer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |