Remove Distinct Count From Grand Total

[Mirithu]

Hi,

I have a common problem with incorrect grand totals but cannot seem to get a solution from previous posts.

I have data with customer numbers and dates columns. I am calculating the distinct count of customers using the measure:

No of Customers = DISTINCTCOUNT( Data[CUSTOMER] )

When I pull the dates and measure into a matrix, I get the distinct count of customer records per month, but the total also adds up the distinct count across all months, thereby giving a lesser figure. 

In the matrix below, I would like to have the total as 56, not the 42 that is showing.

Month Name      No of Customers
January               13
February             13
March                 14
April                    16
Total                    42

Here is the sample data:

CUSTOMER,DATE
XVF901-JJ26-2,03-Jan-22
XVF904-JJ21-8,04-Jan-22
XVF901-JJ17-3,05-Jan-22
XVF903-JJ22-3,06-Jan-22
XVF902-JJ97-2,07-Jan-22
XVF904-JJ21-8,11-Jan-22
XVF905-JJ10-0,11-Jan-22
XVF900-JJ48-9,14-Jan-22
XVF903-JJ05-7,14-Jan-22
XVF904-JJ21-8,17-Jan-22
XVF903-JJ38-1,19-Jan-22
XVF902-JJ97-2,20-Jan-22
XVF902-JJ97-2,20-Jan-22
XVF904-JJ21-8,20-Jan-22
XVF904-JJ21-8,20-Jan-22
XVF901-JJ13-4,21-Jan-22
XVF904-JJ21-8,21-Jan-22
XVF904-JJ21-8,21-Jan-22
XVF904-JJ21-8,24-Jan-22
XVF902-JJ97-2,25-Jan-22
XVF903-JJ51-7,25-Jan-22
XVF904-JJ21-9,25-Jan-22
XVF903-JJ00-9,26-Jan-22
XVF904-JJ21-8,26-Jan-22
XVF903-JJ22-3,28-Jan-22
XVF903-JJ00-9,02-Feb-22
XVF901-JJ99-8,04-Feb-22
XVF900-JJ01-4,07-Feb-22
XVF903-JJ51-7,08-Feb-22
XVF904-JJ21-8,08-Feb-22
XVF900-JJ21-5,09-Feb-22
XVF902-JJ97-0,09-Feb-22
XVF903-JJ22-3,09-Feb-22
XVF903-JJ22-3,09-Feb-22
XVF904-JJ44-7,10-Feb-22
XVF904-JJ76-2,10-Feb-22
XVF902-JJ97-2,11-Feb-22
XVF904-JJ76-2,11-Feb-22
XVF904-JJ76-2,11-Feb-22
XVF904-JJ21-8,14-Feb-22
XVF904-JJ21-8,14-Feb-22
XVF903-JJ51-7,15-Feb-22
XVF904-JJ76-2,15-Feb-22
XVF904-JJ21-8,17-Feb-22
XVF904-JJ21-8,18-Feb-22
XVF900-JJ11-7,22-Feb-22
XVF903-JJ51-7,22-Feb-22
XVF904-JJ44-7,23-Feb-22
XVF904-JJ21-8,24-Feb-22
XVF903-JJ28-2,28-Feb-22
XVF903-JJ28-2,28-Feb-22
XVF904-JJ21-8,01-Mar-22
XVF904-JJ21-8,01-Mar-22
XVF901-JJ49-6,02-Mar-22
XVF904-JJ21-8,03-Mar-22
XVF904-JJ21-8,03-Mar-22
XVF904-JJ21-8,03-Mar-22
XVF900-JJ17-7,08-Mar-22
XVF902-JJ97-2,08-Mar-22
XVF900-JJ11-3,10-Mar-22
XVF904-JJ21-8,10-Mar-22
XVF900-JJ37-4,11-Mar-22
XVF904-JJ10-3,11-Mar-22
XVF904-JJ21-8,14-Mar-22
XVF904-JJ21-8,14-Mar-22
XVF901-JJ81-4,17-Mar-22
XVF902-JJ17-1,17-Mar-22
XVF903-JJ54-9,23-Mar-22
XVF903-JJ54-9,23-Mar-22
XVF903-JJ89-8,23-Mar-22
XVF903-JJ89-8,23-Mar-22
XVF903-JJ94-2,23-Mar-22
XVF904-JJ21-8,23-Mar-22
XVF904-JJ21-8,23-Mar-22
XVF903-JJ51-7,28-Mar-22
XVF904-JJ21-8,28-Mar-22
XVF901-JJ81-2,31-Mar-22
XVF904-JJ21-8,01-Apr-22
XVF904-JJ21-8,01-Apr-22
XVF901-JJ94-1,02-Apr-22
XVF902-JJ29-8,02-Apr-22
XVF902-JJ97-2,04-Apr-22
XVF904-JJ21-8,04-Apr-22
XVF904-JJ21-8,05-Apr-22
XVF902-JJ55-2,06-Apr-22
XVF900-JJ19-2,07-Apr-22
XVF901-JJ81-2,07-Apr-22
XVF902-JJ26-6,07-Apr-22
XVF903-JJ51-7,07-Apr-22
XVF903-JJ53-3,08-Apr-22
XVF901-JJ49-4,09-Apr-22
XVF901-JJ64-1,09-Apr-22
XVF903-JJ26-2,11-Apr-22
XVF903-JJ28-2,11-Apr-22
XVF904-JJ21-8,11-Apr-22
XVF904-JJ21-8,12-Apr-22
XVF904-JJ21-8,12-Apr-22
XVF904-JJ39-6,12-Apr-22
XVF903-JJ51-7,14-Apr-22
XVF904-JJ76-2,14-Apr-22
XVF904-JJ76-2,14-Apr-22
XVF904-JJ76-2,14-Apr-22
XVF904-JJ76-2,14-Apr-22

How do I resolve this?

Thank you.

[philouduv]

Hey @Mirithu ,

First create a table with every months (seclect new table and write 

months = SELECTCOLUMNS(distinct(datasource[datetable].[Month]),"month",(datasource[datetable].[Month]))

Then create a new column on this table nbr_customer which will be the following :

nbr_custo = calculate(DISTINCTCOUNT(datasource[Customer]),

FILTER(ALL(datasource),

months[month] = datasource[datetable].[Month]))

After this point you should have a column with the count on every month (months with no customer will be blank)

Then just add in the matrix the month column(rows) and nbr_custo(values) it should do the trick


Best regards,

[Mirithu · Accepted solution]

Thank you @philouduv 

I also used this formula and it also works:

Correct Total =

IF( HASONEVALUE( Calendar[Month Name] ),

[No of Customers],

SUMX( VALUES( Calendar[Month Name] ), [No of Customers] )

)

[Mirithu]

@philouduv Thank you. You are absolutely right. 

I would like to know how to write the DAX formula such that it still gives me the distinct count for every month but the total be the sum of the monthly distinct counts i.e. the total = 56.

[philouduv]

It is because in total you have 42 different customer in your entire table so the total reflect that without filtering by month ,some customers appears 2 times in your different months that's why the total by month is higher than for the entire year.

Best regards,