Forum Discussion
Helper IHow To Filter Based on Sum Column
Hi. I'm new to Power BI, so sorry if this is a stuipd question with an easy answer.
I have a table with the following relevant columns: User ID, Quantity, and Warehouse Document Number
When I put User ID and Quantity in the Values area of my report, but not Warehouse Document Number, it sums up all the Quantities for all the rows with a matching User ID, as expected.
Now I want to filter my report based on that *summed up value*. For example if User ID Sam has a summed up qty of 45 and User ID Josh has a summed up value of 12, I might want a filter that doesn't show any User IDs with quantity below 15, so it takes out Josh and leaves Sam.
But when I put Quantity in the Report Level Filters, rather than filtering based on the summed up quantity value for each User ID, it filters out *each individual row* that's below 15.
So how do I filter based on the Quantity at the User ID level?
To Get the Solution, I have utilized the GROUP BY feature of the query editor.
Follow the screenshots for the solution.GROUP BYCONDITIONAL COLUMNFILTER THE COLUMN
ytc-reports There are several other way to do this...
1) Once you create your table visualization with User and Quantity
go to Visaul Level Filters and select Show items when the value: is greater than 15
This would work the same way whether you just use the
Quantity Column or create a
Quantity Measure = SUM ( Table[Quantity]) or
Quantiy Measure per User = CALCULATE ( SUM('Table'[Quantity]), ALLEXCEPT('Table', 'Table'[User]) )
2) You can create another Measure based on the last one above
Quantity per User > 15 = CALCULATE ( [Quantiy Measure per User], FILTER ('Table', [Quantiy Measure per User] > 15) )
then when you create a table visualization with just User and this Measure
you'll get the result you want with no need to use the Visual Level Filters
It's up to you which way fit best in your case! :smileyhappy:
5 Replies
ytc-reportsJust for example, let's say I had the following list of rows:
User Warehouse Number Quantity Sam ABC 14 Sam BCD 14 Josh CDE 10 Josh DEF 3 When I take out the Warehouse Document Number, it looks like this:
User ID Quantity Sam 28 Josh 13 I want to add a filter that filters out the *second table* based on Quantity below 15, so that it would only show the Sam row (and any other User with a sum > 15). But at the moment, when I filter based on Quantity below 15, it would filter out *each individual row* from the top table, leaving me with nothing.
- BhaveshPatel
Super User
To Get the Solution, I have utilized the GROUP BY feature of the query editor.
Follow the screenshots for the solution.GROUP BYCONDITIONAL COLUMNFILTER THE COLUMN
SeanCommunity Champion
ytc-reports There are several other way to do this...
1) Once you create your table visualization with User and Quantity
go to Visaul Level Filters and select Show items when the value: is greater than 15
This would work the same way whether you just use the
Quantity Column or create a
Quantity Measure = SUM ( Table[Quantity]) or
Quantiy Measure per User = CALCULATE ( SUM('Table'[Quantity]), ALLEXCEPT('Table', 'Table'[User]) )
2) You can create another Measure based on the last one above
Quantity per User > 15 = CALCULATE ( [Quantiy Measure per User], FILTER ('Table', [Quantiy Measure per User] > 15) )
then when you create a table visualization with just User and this Measure
you'll get the result you want with no need to use the Visual Level Filters
It's up to you which way fit best in your case! :smileyhappy:
- ytc-reports
It turns out the 'visual level filters' is all I needed. Thank you all for your help.