cancel
Showing results for
Did you mean: Frequent Visitor

## Summarize field with depending filters

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?

1 ACCEPTED SOLUTION Frequent Visitor

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... 🙂

3 REPLIES 3  Super User II

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?

In doing so, you are also helping me. Thank you!

Proud to be a Super User! Frequent Visitor

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... 🙂  Super User IV

@HoracioRek , You need to create an independent table with these values and based on the selection you need to create a measure

Proud to be a Super User! Announcements #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### Power BI Dev Camp - September 30th, 2021  