Adding measures and calculated columns

[Clint]

Hello,

I am being asked to create a calculated RYG for each project and as part of that, I've created table that provides a KPI for scope (measuring how far over or under).  From this KPI, we generate a score using a calculated column

c_Scope Score =

SWITCH(

TRUE(),

'BurndownScope'[Ratio]<=1.3,1,

'BurndownScope'[Ratio]<=1.5,2,

'BurndownScope'[Ratio]>1.5,3

)

I then created a calculated column that adds the 4 components of the RYG together to generate an overall score.  The problem I'm running into is with the scope score.  Because it is a column, I can't add it w/o some sort of aggregator, which then gives me the wrong number for the overall score.  How do I pass the scope score for each project to the calculation for the Overall Project score?

Dax for overall project score metric

c_Overall Project Score =

'Risks'[m_Project Risk Score]

+

'Issues'[m_Project Issue Score]

+

'Projects'[M_Sched Perf]

+

'burndownscope'[c_Scope Score]

[v-juanli-msft]

Hi @Clint 

What is the relationship amonth the four tables?

'Risks',Issues',Projects',burndownscope'

When adding four columns in one table visual, how does it look like?

'Risks'[m_Project Risk Score]

'Issues'[m_Project Issue Score]

'Projects'[M_Sched Perf]

'burndownscope'[c_Scope Score]

Could you give some data samples so that i can reproduce your problem?

Best Regards
Maggie

[Clint]

Hi Maggie,

Risk and Issues are related to the Projects Table via the ProjectID field.  The scope score is generated from a table that tracks the current scope and baseilne scope and is related to a table that tracks weekly burndown (and provides scope values) via the clarity ID in a many to one.  The Burdowns table is related to the Project Table via the Clarity ID (see embedded image).  This is a many to many relationship unfortunately.  

I am using the "average" function to generate the overall project score in the Project Score table.

Project Score

Relationships

[Clint]

The table that has the burn down scope looks like this:

There's only one value for each project so I'm not clear as to why I need an aggregator?

---

@Clint wrote:

Hello,

I am being asked to create a calculated RYG for each project and as part of that, I've created table that provides a KPI for scope (measuring how far over or under).  From this KPI, we generate a score using a calculated column

c_Scope Score =

SWITCH(

TRUE(),

'BurndownScope'[Ratio]<=1.3,1,

'BurndownScope'[Ratio]<=1.5,2,

'BurndownScope'[Ratio]>1.5,3

)

I then created a calculated column that adds the 4 components of the RYG together to generate an overall score.  The problem I'm running into is with the scope score.  Because it is a column, I can't add it w/o some sort of aggregator, which then gives me the wrong number for the overall score.  How do I pass the scope score for each project to the calculation for the Overall Project score?

Dax for overall project score metric

c_Overall Project Score =

'Risks'[m_Project Risk Score]

+

'Issues'[m_Project Issue Score]

+

'Projects'[M_Sched Perf]

+

'burndownscope'[c_Scope Score]

---