Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |