Distinct count total showing incorrect grand total

[RainaM]

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.

[v-shex-msft · Accepted solution]

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

[RainaM]

Thanks Xiaoxin. This was helpful!

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[RainaM]

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

Thanks!