Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
Gurote
New Member

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?

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

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

Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

Thank you! This solved my problem

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.