Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Total Visual Incorrect Totals

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.

Need Help

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    7 years ago

    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

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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's avatar
      Wkbdrguy
      Microsoft Employee
      6 years ago
      I'm seeing the same problem. A measure does fix this but still seems like a BUG for totals and subtotals.