Forum Discussion
Filter visual by a total aggregated value
Hello, I search other topics and I can't seem to find a solution.
I have a simple table, is a view and all metrics are already there. With those metrics I created three aggregated measures and created three visuals that are filtered by slicers.
Everything works fine so far. One of the tables shows the availability% by machine by shift. With certain values in the slicers, the other visuals show only three machines, whereas the availability% table shows two more with availability% = 0 (this is expected due to the structure of the view)
The thing is, when I want to filter the Availability% table to show only the result aggregated value different from zero, the filter of the visual doesn't work. I pretty new to powerbi and what I imagine powerBI is doing is filtering values at row level and not the aggregated result.
This is what I wanted to achieve:
Is this the expected behavior? If so, how can I filter by the aggregated value?
Edit: I cannot attached a file, but here's the direct link to it
https://drive.google.com/file/d/151_BNSKfKGdYOyfqlWw7Z41WXuyHeVsH/view?usp=drive_link
Thanks in advance!
what I imagine powerBI is doing is filtering values at row level and not the aggregated result.Mostly Correct. You usually need to create another filter measure that returns 0 or 1 (for example) based on aggregate calculations ( usually done via REMOVEFILTERS ) on each detail row.
In your case however the "False" branch of the IF statement returned 0. When you remove that you get the desired result.
Availability% = IF(SUM('Sheet 15_Full Data'[Available_Prod_Dur_Machine ( uptime+downtime)]) <> 0, Divide(SUM('Sheet 15_Full Data'[Prod_Dur_Machine]),SUM('Sheet 15_Full Data'[Available_Prod_Dur_Machine ( uptime+downtime)]),0))Also note that the use of DIVIDE is preferred over "/" as it allows you to gracefully handle bad divisions.
2 Replies
- lbendlin
Super User
what I imagine powerBI is doing is filtering values at row level and not the aggregated result.Mostly Correct. You usually need to create another filter measure that returns 0 or 1 (for example) based on aggregate calculations ( usually done via REMOVEFILTERS ) on each detail row.
In your case however the "False" branch of the IF statement returned 0. When you remove that you get the desired result.
Availability% = IF(SUM('Sheet 15_Full Data'[Available_Prod_Dur_Machine ( uptime+downtime)]) <> 0, Divide(SUM('Sheet 15_Full Data'[Prod_Dur_Machine]),SUM('Sheet 15_Full Data'[Available_Prod_Dur_Machine ( uptime+downtime)]),0))Also note that the use of DIVIDE is preferred over "/" as it allows you to gracefully handle bad divisions.
I see my error now!
Thanks a lot for your time and recommendations!
