cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HoracioRek
Frequent Visitor

Summarize field with depending filters

Using dummy data to explain:

TeamCategoryAmount
AGreen1
ABlue2
ARed3
ATotal6
BTotal10
CGreen5
CBlue4

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

View solution in original post

3 REPLIES 3
PaulDBrown
Super User II
Super User II

@HoracioRek 

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?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

View solution in original post

amitchandak
Super User IV
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

same as measure slicer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

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 768x460.png

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