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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Clint
Helper V
Helper V

Adding measures and calculated columns

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]
3 REPLIES 3
v-juanli-msft
Community Support
Community Support

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

 

 

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

 

project score table.PNGRelationships

relationships.PNG

Clint
Helper V
Helper V

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

burndown scope table.PNG

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]

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors