Forum Discussion
Filter visual by a total aggregated value
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.
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!
