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 #### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022. #### Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps #### Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer. Top Solution Authors
Top Kudoed Authors
Users online (1,688)