Count Rows with Sum conditions

[MJMutex]

Hi All

I'm working on a dataset containing transactions over a 5 years period. This includes Financial Year, Supplier and Amount. I've got a matrix visual nicely showing a breakdown.

The user has asked if I can have a card which shows a count of how many suppliers have spent over 100k in a financial year. She has also asked if it would be possible to have a list of Suppliers who have had one or more Financial year with 100k+ (with perhaps a count for each and total).

I'm very new to PowerBI, can anyone point me in the right direction?

Many Thanks

[v-xuding-msft · Accepted solution]

Hi @MJMutex ,

I created a sample that you can have a try.

>100k = CALCULATE(COUNT('Table 1'[Suppiler]),FILTER(ALLEXCEPT('Table 1','Table 1'[Financial Year]),'Table 1'[Amount] >= 100000 ))
Supplier list = CALCULATE(MAX('Table 1'[Suppiler]),FILTER('Table 1','Table 1'[Amount] >= 100000))

I attached my sample that you can download. If it is not what you want, please share some sample data and your expected results.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MJMutex]

Hi @v-xuding-msft

Thank you very much for the reply.  Not sure its quite right, likely my fault as saying the rows included financial year implied each row was a sum for that supplier, its not. 

Each row is an individual transaction. There are multiple transactions for each supplier and it needs to be checking the SUM of each supplier for each Financial Year.

For example, with this raw data:

TOTALS
2017: Sam 92000 / Marry 101000 / Jack 37000

2018: Sam 540000 / Marry 870000 / Jack 90000
2019: Sam 104000 / Jack 640000

So i would expect a count to return 5. As there were five instances where a supplier spent >=100k in a Financial Year.  

[v-xuding-msft]

Hi @MJMutex ,

Suggest you to create a calculated table to implement it.

Summarize Table = SUMMARIZE('Table','Table'[Financial Year],'Table'[Supplier],"Total",CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Financial Year],'Table'[Supplier])))

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.