How To Filter Based on Sum Column

[ytc-reports]

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?

[BhaveshPatel · Accepted solution]

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]

Just 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.

[Sean · Accepted solution]

@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!

[digant]

Hi @Sean

I have exact same issue. Client can have multiple accounts and data is in same table. I want to show Losers and Winners based on date range.

Lets say between selected date range, if combined PnL across all accounts for that client is greater than 0 then Winners and show in list.

If combined PnL across all accounts for that client is less than 0 then Loers and show it in list. If I put condition in Visual Filter as SumOfGrossRevenue < 0 and >0, it gives incorrect result.

Total Without Filter is correct. After Filter is incorrect for Client Level

Can you please help me explain what I am doing wrong here ?

Thank you in advance.

Regards

Digant

[ytc-reports]

It turns out the 'visual level filters' is all I needed. Thank you all for your help.

[BhaveshPatel · Accepted solution]

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