Total Visual Incorrect Totals

[Anonymous]

Hi All,

I am using a simple table visual to count the disease's by individual but the total being returned is incorrect.

This total should be 841 but if you actually add the numbers together its 867 so there's a difference of 26. The other visuals counting the same individuals and return the correct count so I am not sure what the difference is.

There is a count distinct on all every instance of that column.

Thanks for any assistance.

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

This has the same logic with measure totals problem. Very common. See this post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

and I also provide a simple example:

ID	Type
1	A
1	B
1	B
2	A
2	A
2	C

table visual of distinct count

ID	COUNT
1	2
2	2
TOTAL	3

For ID=1, there are 2 distinct count type(A,B)

For ID=2, there are 2 distinct count type(A,C)

but for table total,  there are 3 distinct count type(A,B,C), not 2+2=4

and you could use this formula to add a measure

Measure = SUMX(SUMMARIZE('Table','Table'[ID]),CALCULATE(DISTINCTCOUNT('Table'[Type])))

Result:

Best Regards,

Lin

[Wkbdrguy]

I'm seeing the same problem. A measure does fix this but still seems like a BUG for totals and subtotals.

[TomMartens]

Hey,

Count (Distinct) makes sure that no individual will be counted double, at least not where the underlying table is filtered.

From the first visual the "table / matrix" visual where the number of rows does not meet the total row (manually adding up the counts (distinct count)), total equals 841 vs sum of rows equals 867.

I guess that this is due to the fact that there is are records for the same indiviual with different stages of disease as there is no "date / time" filtering involved, an individual could be tracked with "Early/..." and to a later point in time with the stage "Deceased".  As there is no filter for the "Stage of Disease" in the Total row, it's more than natural that the number will not add up, even it's odd on the first glance.

To check my assumption just create a simple table visual with the ids of the individuals and simply count the "Stage of Disease" column, by changing the aggregation to Count (Distinct), I guess you will find individuals where the count is greater than 1

Regards,

Tom