Wrong calculation for distnct count in grand total table

[AgustinBrocardo]

Hi!

I´m having a problem trying to use distinctcount. I have a database with customers by region, one customer can make a purchase in different regions and, in some cases you can have the same customer for the same region as you will see below. This is due to the rest of the database because it also considers other fields. 

For this analysis i want to have a simple table showing a distinctcount of customers by region but the problem is when the table makes the grand total, it doesn´t show de sum of every region, it shows the distinct count of all the customers (which is less because some customers should be repeated if they buy in another region)

I want the grand total to be the sum of the whole column (Count of column), which is 89, and not the distinct count of the customers of the database withount considering the region, which is 80.

Also, i´m not using any measure. i´m just selecting the format of the field as count (distinct) when i add it to the table visual.

Below there is a sample of the data for the calculation.

Custom REGION
Customer 1 C
Customer 2 A
Customer 2 A
Customer 3 B
Customer 3 B
Customer 4 C
Customer 4 A
Customer 4 B
Customer 4 B
Customer 4 B
Customer 4 B
Customer 4 C
Customer 4 D
Customer 4 B
Customer 4 D
Customer 4 D
Customer 4 D
Customer 5 D
Customer 6 C
Customer 6 D
Customer 6 D
Customer 7 C
Customer 8 A
Customer 8 B
Customer 8 C
Customer 8 A
Customer 8 A
Customer 8 B
Customer 8 A
Customer 9 A
Customer 9 A
Customer 10 C
Customer 11 C
Customer 11 A
Customer 12 D
Customer 12 D
Customer 12 D
Customer 13 A
Customer 14 A
Customer 15 A
Customer 16 B
Customer 16 B
Customer 16 B
Customer 17 B
Customer 17 B
Customer 18 A
Customer 19 A
Customer 20 B
Customer 20 B
Customer 20 B
Customer 21 D
Customer 22 D
Customer 23 D
Customer 24 B
Customer 25 D
Customer 25 D
Customer 25 D
Customer 26 B
Customer 27 A
Customer 27 A
Customer 28 A
Customer 28 A
Customer 28 A
Customer 29 C
Customer 30 C
Customer 30 C
Customer 31 C
Customer 31 C
Customer 31 C
Customer 32 A
Customer 32 A
Customer 32 A
Customer 32 A
Customer 32 A
Customer 32 A
Customer 32 D
Customer 32 D
Customer 32 D
Customer 33 C
Customer 33 C
Customer 33 C
Customer 33 C
Customer 33 C
Customer 33 C
Customer 33 C
Customer 33 C
Customer 34 C
Customer 34 C
Customer 35 C
Customer 36 C
Customer 36 C
Customer 36 C
Customer 36 C
Customer 37 A
Customer 37 A
Customer 38 B
Customer 38 B
Customer 39 C
Customer 39 C
Customer 39 C
Customer 40 C
Customer 40 C
Customer 41 D
Customer 41 D
Customer 42 B
Customer 42 B
Customer 42 B
Customer 43 A
Customer 44 C
Customer 44 C
Customer 45 D
Customer 45 D
Customer 46 A
Customer 46 A
Customer 47 C
Customer 47 C
Customer 48 A
Customer 48 A
Customer 48 A
Customer 49 C
Customer 50 C
Customer 51 A
Customer 52 C
Customer 53 A
Customer 54 D
Customer 55 A
Customer 56 C
Customer 56 C
Customer 57 D
Customer 58 A
Customer 59 D
Customer 59 D
Customer 60 C
Customer 60 C
Customer 60 C
Customer 60 C
Customer 61 C
Customer 61 C
Customer 61 C
Customer 61 C
Customer 61 C
Customer 62 A
Customer 63 D
Customer 64 A
Customer 65 A
Customer 66 D
Customer 66 D
Customer 67 D
Customer 67 D
Customer 68 A
Customer 69 D
Customer 70 A
Customer 71 C
Customer 71 C
Customer 72 B
Customer 73 D
Customer 73 D
Customer 73 D
Customer 74 D
Customer 75 D
Customer 75 A
Customer 75 A
Customer 76 C
Customer 76 C
Customer 76 C
Customer 77 B
Customer 78 D
Customer 78 D
Customer 78 D
Customer 79 C
Customer 79 C
Customer 80 A

[v-robertq-msft · Accepted solution]

Hi, @AgustinBrocardo 

According to your description, I can clearly understand your requirement, I think you can create a measure to calculate the distinct count value of the custom and use a new measure to fix the total problem, you can try my steps:

Create two measures:

REGION = RIGHT([Customer],1)

Count of custom1 =

var _table=SUMMARIZE('Table',[REGION],"1",[Count of custom])

return

IF(

    HASONEVALUE('Table'[REGION]),[Count of custom],SUMX(_table,[1]))

Then you can create a table chart and place the second measure:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @AgustinBrocardo 

That is a Total issue so try this measure:

Measure = 
IF (
    HASONEVALUE ( 'Table'[REGION] ),
    DISTINCTCOUNT ( 'Table'[Custom] ),
    SUMX (
        SUMMARIZE (
            'Table',
            'Table'[REGION],
            "DisC", DISTINCTCOUNT ( 'Table'[Custom] )
        ),
        [DisC]
    )
)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!