SUM function per selection

tonijj · ‎10-16-2017

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 (ImportanceScore), 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.

Attached an image - The figures in the red circles should NOT be in the report, nor in in the total sum highlighted in the formula below.

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

Getting desperate/frustrated, any help is appreciated!!

The formula below (Measure)

WeightOfQuestion =

DIVIDE(QuestionTable[ImportanceScore];

CALCULATE(

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

)

Eric_Zhang · ‎10-19-2017

@tonijj wrote:

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 (ImportanceScore), 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.

Attached an image - The figures in the red circles should NOT be in the report, nor in in the total sum highlighted in the formula below.

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

Getting desperate/frustrated, any help is appreciated!!

The formula below (Measure)

WeightOfQuestion =

DIVIDE(QuestionTable[ImportanceScore];



              CALCULATE(

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

              )

)

@tonijj

Thanks for sharing the sample file. However I don't see the figures in red circles and I'm still not clear about what you'd like to get when saying "to ONLY Sum the Scoring per a given Dimension". Could you post further clarification?

Eric_Zhang · ‎10-17-2017

@tonijj wrote:

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 (ImportanceScore), 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.

Attached an image - The figures in the red circles should NOT be in the report, nor in in the total sum highlighted in the formula below.

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

Getting desperate/frustrated, any help is appreciated!!

The formula below (Measure)

WeightOfQuestion =

DIVIDE(QuestionTable[ImportanceScore];



              CALCULATE(

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

              )

)

@tonijj

Your description is not clear, could you post some sample data of the mentioned tables? Even better you can share a pbix file. You can upload the pbix file to Onedrive/GoogleDrive and share the download link. Do remember to mask sensitive data before uploading.