Filter Based on Sum of Grouped Values

[Jenni-Sky]

Hi, 

I have the following data: 

Order	GBP
533365	40126.95
533444	12360
533456	12173.91
533387	9776.29
533456	8311.3
533467	6482.35
533399	6380
533314	4997.74
533456	2381.74
533314	2004.52
533314	1720.96

I would like to create a Card to Count the number of Distinct Orders which have Sum of GBP > 5000. I would expect value returned to be 7.

And a second Card to Count the number of Distinct Orders which have Sum of GBP > 20000. I would expect value returned to be 2.

When I try to apply a filter on GBP, showing "Count Distinct" will give a total of 6 for 5k and 1 for 20k. Showing "Count" gives total of 7 for 5k and 1 for 20k. These are both incorrect - I can see it is looking at the value of the individual lines as opposed to looking at two rows with the same order number:

533456

12173.91

533456

8311.3

Adding their GBP together, and then applying the filter to the sum of these. 

I have tried to create a measure for Sum of GBP in my table. However when I drag and drop this measure into the Filtering tab, it won't let me make any changes to that filter. 

Thank you in advance for any help/suggestions! 

[V-pazhen-msft]

@Jenni-Sky 

There are many ways can get to the result. I use 2 measures to explain, a combined measure it provided above by someone else.

The logic is the GBP column is not summed by ordernumber, so each row is read separately. All you need is to create a measure that sum the GBP by Order:

Sum by Order Measure = CALCULATE(SUM('Order Rec'[GBP]),ALLEXCEPT('Order Rec','Order Rec'[Order]))

Then you just create a count measure use the sum by order to filter:

DistinctCount Order = CALCULATE(DISTINCTCOUNT('Order Rec'[Order]),FILTER('Order Rec',Sum by Order Measure>5000))

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Jenni-Sky , Try like

countx(filter(summarize(Table, table[Order],"_1",sum(table[GBP])),[_1]>5000),[Order])

Or use the visual level filter for the sum measure

[Jenni-Sky]

@amitchandak , I've tried the visual level filter and it doesn't seem to work for me? 

Where would I use the countx? I'm not too familiar with DAX and am relatively new to Power BI so am still learning. 

Would this work in conjunciton with the existing Page Level Date filter which will also be applied to the cards I am hoping to create? (There is a "Date" column in this table too ) 

[AllisonKennedy · Accepted solution]

Either use DAX summarizecolumns or if you have a table with each order number only listed once, then you can use a SUMX. Then put that measure in the card and filter.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Jenni-Sky]

@AllisonKennedy , I think this solution will work for me, however I am new to Power BI and am still only learning DAX. 

My table is called 'Orders Rec'. 

Column names from this table used on this page of the report are

- Order (which is the column values should be grouped by)

- GBP (which I would want to filter by Sum of GBP for all orders of the same value)

I also have a Date Filter on this page of the report, so Orders Rec also has a Date column that needs to be taken into consideration. Would that have an impact on this solution? 

[AllisonKennedy]

@Jenni-Sky 

Try this: 

Card = COUNTROWS(FILTER(SUMMARIZECOLUMNS('Order Rec'[Order],"SumGBP",SUM('Order Rec'[GBP])),[SumGBP]>5000))

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv