Forum Discussion

Mirithu's avatar
Mirithu
Helper II
4 years ago
Solved

Remove Distinct Count From Grand Total

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.
 
 
 
 
 
 
Need Help
  • Mirithu's avatar
    Mirithu
    4 years ago

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

4 Replies

  • philouduv's avatar
    philouduv
    Resolver III
    4 years ago

    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,

  • Mirithu's avatar
    Mirithu
    Helper II
    4 years ago

    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's avatar
    philouduv
    Resolver III
    4 years ago

    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's avatar
      Mirithu
      Helper II
      4 years ago

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