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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mastone
Frequent Visitor

Incorrect total in Matrix

I have this matrix

mastone_3-1709066425500.png

 

I am having trouble getting the score totals to come out.  I tried to do the Sumx(summarize(), calc) calcuation but from the context of Survey I can't reach standards.  More details below.

 

My scored calculation is additive (so not non-additive), I thought but I am guessing it is not.  It looks like this:

 
Scored =
CALCULATE ( SUM ( Surveys[Response Asked Normalized] ), Surveys[Response Status] = 3 )
 
The relationships of my table look like this and are described below 
 
mastone_1-1709066087862.png

 

A Survey has 1 Question
Question has many Standards
The standards are a heirarchy of Chapter, Standard, Element of Performance, and back to Question


A better visualization with the questions are as follows.  Again...incorrect totals though.

mastone_2-1709066396488.png

I am wondering if the relationship between Questions and QuestionStandards tables is causing the incorrect sums???

I tried to do the SUMX(  Summarize(), calc) to see if that will work, but from the context of Survey, I can't add in the standards tables as they are accessible.  This again makes me believe it's the Questions and QuestionStandards relationship that has things messed up.

 

Any Ideas?

 



1 ACCEPTED SOLUTION

Thank you for the response.   The above didn't really solve the issue but I was able to get the result I needed.  Here is my final measure, which uses the SumX(summarize(), calc) pattern.

Scored Standard =
SUMX(
SUMMARIZE(QuestionStandards,
QuestionStandards[QuestionId],
'Chapters'[Chapter],
Standards[Standard],
ElementOfPerformance[Element Of Performance])
, [Scored])

The stumbling block for me was going from the survey table at the start.  I couldn't cross through the the questionstandards table to walk the heirarchy.  But starting from the questionsstandards table allowed me to do it, and then the [scored] measure grabbed what it needed out of the survey table.

But....as it turns out, Power Bi is smarter than me.  It was giving me the correct values as the results were non-additive.  The items that I thought were unique were not.  They were showing in both the EP 1 and EP 3 categories, so the fact that it didn't sum up was correct.   So the formula above gives me the result I was after, but it's not the correct result.

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hi @mastone ,

Please create a measure.

result = 
var _b = SUMMARIZE('Surveys','Surveys'[Chapter],"aaa",'Surveys'[Scored])
return
IF(HASONEVALUE('Surveys'[Chapter]),[Scored],SUMX(_b,[aaa]))

More details about measure total: Power BI: Totals Incorrect and how to Fix it - Finance BI (finance-bi.com)

 

 

How to Get Your Question Answered Quickly - Microsoft Fabric Community

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Rongtie

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

 

 

 

 

Thank you for the response.   The above didn't really solve the issue but I was able to get the result I needed.  Here is my final measure, which uses the SumX(summarize(), calc) pattern.

Scored Standard =
SUMX(
SUMMARIZE(QuestionStandards,
QuestionStandards[QuestionId],
'Chapters'[Chapter],
Standards[Standard],
ElementOfPerformance[Element Of Performance])
, [Scored])

The stumbling block for me was going from the survey table at the start.  I couldn't cross through the the questionstandards table to walk the heirarchy.  But starting from the questionsstandards table allowed me to do it, and then the [scored] measure grabbed what it needed out of the survey table.

But....as it turns out, Power Bi is smarter than me.  It was giving me the correct values as the results were non-additive.  The items that I thought were unique were not.  They were showing in both the EP 1 and EP 3 categories, so the fact that it didn't sum up was correct.   So the formula above gives me the result I was after, but it's not the correct result.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors