distinct count - difference per month and per year

[alevandenes]

Hi all,

does someone has any ideas on how this can happen?

i am using COUNT DISTINCT and as you can see below the TOTAL does not match the Sum of the months.

the total should be 788 but somehow it is 790 - anything that comes to mind on why the formula counts more than there are?

Thank you in advance

[v-yiruan-msft]

Hi @alevandenes ,

You can create another new measure base on the current measure, then replace the current measure to put it on the matrix:

New Measure = SUMX ( VALUES ( 'Table'[Month Name] ), [Current Meaure] )

If the above one still can't get the correct result, please provide some sample data(exclude sensitive data), matrix setting and the format of current measure. Thank you.

In addition, you can refer the content in the following links to get it.

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

Best Regards

[alevandenes · Accepted solution]

I have found what the problem was and will close this ticket.

[garvitgupta]

Hi,

I have a similar situation and I am struggling to find a solution. Would you be able to describe how did you solve it?

[SanketBhagwat]

Hi @alevandenes .

1)Create total measure;
Total=SUM(Table[column])

2)Create a measure for total;

Right Total=IF(HASONEVALUE(Table[Complaint_Phase]),[Total],SUMX(VALUES(Table[Complaint_Phase]),[Total]))

Please let me know if that works for you.

Regards,

Sanket Bhagwat

[selimovd]

Hey @alevandenes ,

that depends a lot on the formula. Can you post your measure?

Usually it happens when IDs appear in multiple months, but then usually the single values are more than the total.

Are there possibly values that don't belong to a month? That they have a blank date or something similar?

Best regards

Denis

[alevandenes]

hi @selimovd thank you for getting back to me!

I have no formula in place, i am just using COUNT DISTINCT of complaint number.

and creation date (with day-month-year specified) is a mandatory field so it is not possible that there is either a blank space or a date with just the year but not the month.

I am really scratching my head here 😕 

[selimovd]

@alevandenes yeah, that sounds very strange. Is it possible to share the file? I think this needs a little investigation.