How to count unique values based on aggregated sums

[Gurote]

Hi

I have a dataset containing, amongst much other, following information:

Supplier name       Sum invoice
Supplier 1              500 

Supplier 2              1000

Supplier 3               500    

Supplier 4               2000

Supplier 5               4000

Supplier 1               500 

Supplier 2               1000

Supplier 3               500    

Supplier 4               2000

Supplier 5               4000

I want a unique count of suppliers with total sum of invoices> 1999. Using distinct count and a visual level filter > 1999 gives me a unique count of suppliers for each invoice greater than 1999 = 2 (supplier 4 and 5). The correct count would be 3 (supplier 2 with a total of 2000, supplier 4 with a total of 4000, and supplier 5 with a total of 8000). Preferably, I would have this as a measure, enabeling my to further viusalize the count as I slice down in my report at different departments, categories, etc (which are other columns linked to the ones illustrated above). Does anyone know how to do this, if possible?

[OwenAuger · Accepted solution]

Hi @Gurote

Either of these should work. I'm assuming [Sum invoice] is a column.

The 2nd pattern is useful if you want to filter on those same suppliers, but change the DISTINCTCOUNT to something else

Distinctcount of Suppliers with Total Invoices over 1999 =
COUNTROWS (
    FILTER (
        VALUES ( YourTable[Supplier name] ),
        CALCULATE ( SUM ( YourTable[Sum invoice] ) ) > 1999
    )
)

Distinctcount of Suppliers with Total Invoices over 1999 =
CALCULATE (
    DISTINCTCOUNT ( YourTable[Supplier name] ),
    FILTER (
        VALUES ( YourTable[Supplier name] ),
        CALCULATE ( SUM ( YourTable[Sum invoice] ) ) > 1999
    )
)

[Gurote]

Thank you! This solved my problem