Total of Matrix column doesn't equal sum of all items

[Jdiep]

Hi all,

I am really struggling with an issue in one of my Matrix visuals. As rows I have put in my Date field including hierarchy and a total rows at the bottom.

The issue is that when I make a sum of the column on the month level manually it doens't equal the total of the year. In image1 you can see the Matrix. The PowerBI total is 4565, but the sum of the months is 4830. When I export the data to Excel and sum it without hierarchy it totals 5005. 

The measure is a DistinctCount of the CandidateID's in a table with all Applications, because one candidat can have multiple applications. It seems to count more on some days than what I count from the table itself. I have tried to use another type of visual, but the same problems occurs and ignoring the date hierarchy doesn't seem to fix it.

Is there something I am missing about DateHierarchy in PowerBI that can explain or solve this issue? Would love to hear from you and if you have encountered similiar problems. 

I am already thinking of going into the source system and doing a count there to see which of the amounts in PowerBI is correct and then figure out on what level of hierarchy I should visualize. But it seems to me PowerBI should be able to do this correctly

Look forward to your response or if you need more info.

Image1

[MFelix · Accepted solution]

Hi @Jdiep ,

This is related with the fact that your calculation is based on Distinct ID so when you have the calculations on a yearly level the value is only count one even if it's repeated every month.

On this case you need to have the sum of a SUMX try something similar to:

Total = SUMX(VALUES(Table[Month]), [Measure1])

Depending on your model you may need to change the sintax of the values to your need.

[Jdiep]

Hi @MFelix ,

I actually hadn't thought of this perspective. Tried your SumX and that indeed solved the Totals issue. I can now explain to the business what is happening.

Thank you!