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

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.

Reply
RainaM
Regular Visitor

Distinct count total showing incorrect grand total

I need to show a table with distinct customer IDs that have <=0 volume. while the total comes up to 11, the visual grand total shows 10.  While I tried using the below measure basis some solutions available on the community- 

Distinct = var t=SUMMARIZE(Data,Data[Location],"UserDistinct",DISTINCTCOUNT(Cust_ID[Customer ID] ))
return
SUMX(t,[UserDistinct])  -

when the filter is added to the visual the totals show up correctly, but its not let me add the vol filter in the measure itself. 
I need the customer location & cust_ID coming from Cust_ID table and the brands would come from the brand table. Can someone please help build this dax.

thanks in advance.

Capture.PNG

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @RainaM,

Where is the 'volume' field host? If you mean the data table, you can add a filter on the summaries table 'data' to filter its records.

Distinct =
VAR t =
    SUMMARIZE (
        FILTER ( Data, [volume] <= 0 ),
        Data[Location],
        "UserDistinct", DISTINCTCOUNT ( Cust_ID[Customer ID] )
    )
RETURN
    SUMX ( t, [UserDistinct] )

If it is stored in the customer table, you can modify the calculation expressions to calculate the distinct count with filter conditions.

Distinct =
VAR t =
    SUMMARIZE (
        Data,
        Data[Location],
        "UserDistinct",
            CALCULATE (
                COUNTROWS ( VALUES ( Cust_ID[Customer ID] ) ),
                FILTER ( Cust_ID, [volume] <= 0 )
            )
    )
RETURN
    SUMX ( t, [UserDistinct] )

Regards,

Xiaoxin Sheng

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Hi @RainaM,

Where is the 'volume' field host? If you mean the data table, you can add a filter on the summaries table 'data' to filter its records.

Distinct =
VAR t =
    SUMMARIZE (
        FILTER ( Data, [volume] <= 0 ),
        Data[Location],
        "UserDistinct", DISTINCTCOUNT ( Cust_ID[Customer ID] )
    )
RETURN
    SUMX ( t, [UserDistinct] )

If it is stored in the customer table, you can modify the calculation expressions to calculate the distinct count with filter conditions.

Distinct =
VAR t =
    SUMMARIZE (
        Data,
        Data[Location],
        "UserDistinct",
            CALCULATE (
                COUNTROWS ( VALUES ( Cust_ID[Customer ID] ) ),
                FILTER ( Cust_ID, [volume] <= 0 )
            )
    )
RETURN
    SUMX ( t, [UserDistinct] )

Regards,

Xiaoxin Sheng

Thanks Xiaoxin. This was helpful!

selimovd
Super User
Super User

Hey @RainaM ,

 

can you share the file? That would make it easier.

Otherwise, did you ever check how the SUMMARIZE table looks like with DAX Studio? I guess this could be a good point to start.

 

I guess one CustomerID is appearing at multiple brands, that's the reason you get different number by brand than by total. Can you check that?

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

I have uploaded the file to google drive - https://drive.google.com/file/d/1-NLC5Tz-CLeXqCWJfeoIjjD7RSPDOHld/view?usp=sharing

 

Thanks!

 

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors