Forum Discussion
Count Rows with Sum conditions
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.
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.
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.