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