Forum Discussion

mastone's avatar
mastone
Frequent Visitor
2 years ago
Solved

Incorrect total in Matrix

I have this matrix

 

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 
 

 

A Survey has 1 Question
A 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.

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?

 



Need Help
  • mastone's avatar
    mastone
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

     

     

     

     

    • mastone's avatar
      mastone
      Frequent Visitor
      2 years ago

      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.