Forum Discussion
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...
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
Anonymous
Thank you and I'm sorry for the late reply...
I got heavy into working in DAX time measures. :smileyvery-happy:
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"
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
I'm sorry, It took me some time to be able to test this, however it worked perfectly. Thank you!