DistinctCount wrong row total but correct grand total

[romoguy15]

Hello,

I have a measure where I am trying to get a unique count of trailer numbers. My correct grand total is 26. But my column total by return reason is showing up incorrectly. If I sum it up my self, it comes out to 30. I have sample data that is very simple. idk how I am getting this wrong. I know you can do countx and sumx to get totals by category, but I don't know how to incorrporate that with distinctcountx

https://1drv.ms/u/s!AqID1H0nHPOzg3Ui9ufWS0M9ix3v?e=z1JGvH

[djurecicK2]

Hi @romoguy15 ,

 Is looks like there are trailers with multiple return reasons like F5175. So in the first table, F5175 is counted under Carrier Failure and Store refusal, but the Total which does not have any return reason is 26. This is by design from the DistinctCount function.

The 2nd table comes from a summarization which if you do by itself gives the result below. There is no longer any reference to Trailer ID in this table, so the total (30) is just the sum of the uniquetotal column.

Pleas mark as accepted solution if this has solved the issue.

[romoguy15]

I didn't even catch that. Is there a way to create a duplicated calculated table using summarize to show the unique trailer numbers but only show one of the return reasons?

[Daryl-Lynch-Bzy]

Hi @romoguy15 - you are seeing the correct Values for each filtered context.  However the description "total" is misleading.  Could you please consider reading and watching the following video.

Why Power BI totals might seem inaccurate - SQLBI

[romoguy15]

This was actually a really helpful article. I figured out how to summarize and give the total count to be correct at 29 in the grand total. I think since djurecicK pointed out that some of my unique trailers have two reasons, I have to figure out how to remove one of the reasons so it could total out to a unique count of 26.