Overall Balanced Score (Averages/divided)

[djive]

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]

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]

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]

Hi @djive ,

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

[djive]

@Anonymous

Thank you and I'm sorry for the late reply...

I got heavy into working in DAX time measures. 

Here is some sample data of how it works in Excel.

A B C D E F G H

Store	Balanced	Talk Attainment	Sales Attainment	Adherence Attainment	Log In Attainment	AttachRate Attainment	Lead Attainment
Store 1	99.5%	102.5%	99.5%	94.3%	100.3%	100.0%	100.1%
Store 2	100.4%	102.7%	99.4%	96.1%	100.7%	100.0%	109.2%
Store 3	101.0%	103.7%	99.3%	95.7%	100.7%	100.0%	117.0%
Store 4	99.0%	101.6%	98.9%	93.8%	100.0%	100.0%	101.7%

This is the Formula that makes up the Balanced

=SUM( C5 * 0.3, D5 * 0.3, E5 * 0.2, F5 * 0.1, G5 * 0.05, H5 * 0.05 ) / SUM(1)

Currently, in Power BI, My "Attainments" are measures that I built that are in a Table called "Balance Measures"

[v-shex-msft · Accepted solution]

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

[djive]

I'm sorry, It took me some time to be able to test this, however it worked perfectly. Thank you!