Forum Discussion

Clint's avatar
Clint
Helper V
6 years ago

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]
Need Help

3 Replies

  • Clint's avatar
    Clint
    Helper V
    6 years ago

    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]

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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's avatar
      Clint
      Helper V
      6 years ago

      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