Total at bottom of Matrix is not showing the total of column

[aritz001]

The Staff count column is showing the result of count (Distinct) from my table but the total at the bottom of the column is not summing all the totals above. How can I get the total to sum all the values

[Anonymous · Accepted solution]

Hi @aritz001 ,

According to your screenshot, I know your visual is a matrix. Your issue should be caused by count (Distinct). The subtotal also be impacted by DISTINCTCOUNT() and not show the sum.

Due to I don't know your data model and how many columns in your matrix Row Field. Here I will give you a sample code. Try below code to create a measure. If you add multiple columns in matrix row field, you can add column names to replace "..." in my code. If there is only one column in it, remove ", ..." in my code.

SUM DISTINCT COUNT =
VAR _DISTINCTCOUNT =
    CALCULATE ( DISTINCTCOUNT ( 'TableName'[Column in Matrix Value Field] ) )
RETURN
    SUMX (
        SUMMARIZE (
            'TableName',
            'TableName'[Column1 in Matrix Row Field],
            ...
        ),
        _DISTINCTCOUNT
    )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aritz001]

I used the following formula within a measure as suggested (see below) however my total is incorrect. The total for Staff Count column should be 8,850.

SUM DISTINCT COUNT =
VAR _DISTINCTCOUNT =
CALCULATE ( DISTINCTCOUNT ( 'tblTimeCombined_TimeDetail'[Staff ID] ) )
RETURN
SUMX (
SUMMARIZE (
'tblTimeCombined_TimeDetail',
'tblTimeCombined_TimeDetail'[Date]
),
_DISTINCTCOUNT
)

Here are the more details. Refer to red boxes for more details.

[Greg_Deckler]

@aritz001 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my 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

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[Idrissshatila]

Hello @aritz001 ,

Check this article https://www.sqlbi.com/articles/why-power-bi-totals-might-seem-inaccurate/

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin