Forum Discussion

Gurote's avatar
Gurote
New Member
9 years ago
Solved

How to count unique values based on aggregated sums

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?

Need Help
Tips and Tricks
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    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
    )
)

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    9 years ago

    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
    )
)