Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Jdiep
Helper I
Helper I

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

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

Jdiep_0-1642685674116.png

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.