Forum Discussion

djive's avatar
djive
Frequent Visitor
8 years ago
Solved

Overall Balanced Score (Averages/divided)

Good Evening;

 

I'm relatively new to Power Bi and Can't seem to figure this out.  I Would appreciate any help.

 

I have a table called "Scorecard" and a Table called "Hierarchy" that houses well.. who the agents are.

 

In the Scorecard, I have the agents metric and a relationship built.

 

I Have created a measures folder (called "Scorecard Measures") that houses their attainment over a target, roughly 6 of them. This is one of them as an example.

 

Agent Adherence Attainment =
AVERAGEX(
KEEPFILTERS(VALUES('Hierarchy'[Agent Name])),
CALCULATE(AVERAGE(''[Adherence]))
)/.97

 

.97 is the Target example.

 

My question is I need to get an overall Sum and average of about 6 of those measures. So the above would post 103.4, another 102.3 I need to get the sum and be able to divide. an Excel working example I have is 

 

 

=SUM(Adherence%*Targetweight, TalkTime%*Targetweight, RestTime%*Targetweight)/SUM(Targets) Which works exactly as I need it to.

 

I can't seem to figure out a Dax formula to do the same using the measures I created for the formula above.

 

I appreciate any and all help. 

  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi djive,

     

    Maybe you can try to use below formula  if it works for your scenario:

    BalanceScore =
DIVIDE (
    SUMX (
        FILTER (
            ALL ( 'Scorecard Measures' ),
            [Store] IN VALUES ( 'Scorecard Measures'[Store] )
        ),
        [Agent Metric Attainment 1] * .3
            + [Agent Metric Attainment 2] * .3
            + [Agent Metric Attainment 3] * .2
    ),
    SUMX ( Agent_Targets, Agent_Targets[Weight] )
)

    Regards,

    Xiaoxin Sheng

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    May try the following DAX

     

    Measure =
DIVIDE (
    SUMX ( TableName, TableName[Adherence%] * TableName[Targetweight] )
        + SUMX ( TableName, TableName[TalkTime%] * TableName[Targetweight] )
        + SUMX ( TableName, TableName[RestTime%] * [Targetweight] ),
    SUMX ( TableName, TableName[Target] )
)

     

    • djive's avatar
      djive
      Frequent Visitor
      8 years ago

      Jessica - Thank you.

       

      I tried the following, however, I can't seem to get it to work. This is exactly as I put it in. My only change is the " .3" because the target is in another Table.

       

      This returns a can't display.

       

       

      BalanceScore = DIVIDE (
SUMX ( 'Scorecard Measures', 'Scorecard Measures'[Agent Metric Attainment 1] * .3 )
+ SUMX ( 'Scorecard Measures', 'Scorecard Measures'[Agent Metric Attainment 2] * .3 )
+ SUMX ( 'Scorecard Measures', 'Scorecard Measures'[Agent Metric Attainment 3] * .2 ),
SUMX ( Agent_Targets, Agent_Targets[Weight] )
)

       

      Looking further the error is "Calculation error in measure 'Scorecard Measures'[BalanceScore] Table 'Scorecard Measures' cannot be used in computation because it does not have any columns"

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi djive ,

         

        a little bit difficult to troubleshooting this issue, can you share some sample data for better understanding?