Forum Discussion

tonijj's avatar
tonijj
Helper IV
8 years ago
Solved

SUM - To exclude certain selections

Hi,

 

I have an issue with a Sum(x) function that I really need help with.

 

I am putting together a Rating model with some different dimensions;

Department: I.e (R&D, Finance, IT etc)

Goal: e.g Quality, Finance, Solutioning etc… )

Objective: Strategic, Tactical, Operational

Questions – Each question is assigned a specific Goal and Objective.

Furthermore, Goal, Objective and Questions are all weighted. But lets leave that for the moment.

 

Problem: Each department rates different amount of questions, hence the total for each Department will be different. So, when dividing the score (Per Question) I want to be able to divide for the Sum for that specific department.

 

 

In short, I need to make the formula section which is highlighted, to ONLY Sum the Scoring per a given Dimension, in this case it could be Department, and/or Goal, Objective etc. Depending on what my selection is at the moment. Yeah, the “all(departmentgroup) is obvisouly wrong.

 

Tried “Allselected” but that didn’t work as I hoped for.

 

In the picture below, I don’t want the total sum to include the values in the red circles, in the formula. So the Sum formula below should only include the values that do Not have a red circle, meaning, only the values that are actually included in the selection.


Getting desperate/frustrated, any help is appreciated!!

 

WeightOfQuestion =

 

DIVIDE(QuestionTable[ImportanceScore];

             

              CALCULATE(

                             SUM(QuestionTable[ImportanceScore]); ALL(Department[Group])

              )

)

 

 

 

 

Need Help

7 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Can you supply additional information on those rows that you do/do not want included? Right now the only way that I can see to exclude them would be to only include values for which Score is not blank.

    • tonijj's avatar
      tonijj
      Helper IV
      8 years ago

      Hi,
      Sure! Thanks for taking time on this issue!

      I scrubbed the files and made them available here: files can be found here on Onedrive

       

       

      Its on my OneDrive. 

       

      There is the Excel file with all the data building up to the model. 


      And the BI report. In the tab2, you should see the issue at hand that I described. 

       

      Lets try to describe it better below.

       

      Problem is – that some of the Department Groups (dimension) have different amount of questions, hence, if dividing by the total score (45 in the screenshot) it will give a misguiding result, for those Departments with lesser questions and scoring.

       

      Question Score = Average of Normal Scoring (scoring coming out of excel)

      Result = The weighted result of "Question Score" 

      ImportanceScore = Importance “weight” of each question. – And as you can see in the screenshot, the total shouldn’t be 45 (A) to divide with, but rather 19. And since the formula takes the score and divides by 45 (A) the difference between the “normal” score and weighted score is tremendously large (B) because it uses the wrong total.

       

       

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        Hi tonijj,

        The link you shared is invalid, please update as soon as possible.

        Thanks,
        Angelia