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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.