Weighting average - using variable metrics and variable contracts (Weighted / Balance Scorecard)

[Anonymous]

This is a rather complex / challenging query. So I’m hoping for can pick up the gauntlet and really help me here.

We have a hierarchal measurement system in place for our supplier. Which I hope to make more reflective of the performance, as the current doesn’t allow for greater weighting to be applied for the more influential metrics.

My objectives are:

1. Display the % each metric will contribute towards the score at 4 different levels in PowerBI (screenshot 2 – created from Excel)
2. Identify if I can use a weighted average approach, even though not all metrics are active on each contract.
3. Look for alternative measurement systems if there are any that be suggested from this forum.

Background:

1. I currently have 68 metrics in place, across 29 different contract types.
2. Each contract has varying metrics active, ranging from 4 to 21 metrics, these depend on their type of work / contract.
3. The metrics feed up in a hierarchal view in a pyramid format i.e. Metric > Measure > Theme > Imperative

1. A metrics is scored by ranges as awarded a value of 0,2,4,6,8,10 or N/A

1. At present there are 2 ways we aggregate the scores to enable a consistent measure against all suppliers.
   • Average by Imperative (of which there are 3) – All metrics within an Imperative are average, then the Imperative with scores are averaged to allocate as the score.
   • Average by Theme (of which there are 😎 – All metrics within a Theme are averaged, then the Theme with scores are averaged to allocate as the score.

As mentioned above, there is a growing need to carry weighting for more informing metrics.

I have attached the following to help visualise

• Weighting (Metric-Contracts) Excel document with the contract types and metrics status.
• Weighting (Mock up) Excel document with an example of scores, displaying on the right how the score may look if score by Metric, Measure, Theme or Imperative level
• Mock up - Power BI file showing how the scores look with measure calculating displaying the score by Metric, Measure, Theme or Imperative level

Screetshot 2 - Example of scores weighting:

purple text shwoing weighting of the group, and the invidual metric weighting to the top level

Example scores

Scores within powerBI using a measure to aggregate by category

Power BI mockup 

Excel - Weighting and metric / contract examples 

Full Folder 

[MFelix · Accepted solution]

Hi @Anonymous ,

There is one detail in this that I don't really understand you want to have the mockup table or not?

Using the mockup table with the scores you need to create the following measures (I have made them for Imperative but you can then change them to Theme and Measure):

Scored = COUNTROWS(FILTER('Mock up', 'Mock up'[Score] <> BLANK()))

Weight imperative = 
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Mock up'[Imperative] ),
        DISTINCTCOUNT ( 'Mock up'[Imperative] )
            / CALCULATE (
                DISTINCTCOUNT ( 'Mock up'[Imperative] ),
                ALLSELECTED ( 'Mock up'[Imperative] ),
                'Mock up'[Score] <> BLANK ()
            ), ISINSCOPE('Mock up'[Theme Name]),
    DISTINCTCOUNT ( 'Mock up'[Theme Name] )
        / CALCULATE (
            DISTINCTCOUNT ( 'Mock up'[Theme Name] ),
            ALLSELECTED ( 'Mock up'[Theme Name] ),
            'Mock up'[Score] <> BLANK ()
        ),
   
        DISTINCTCOUNT ( 'Mock up'[Measure Name] )
             / CALCULATE (
                COUNT ( 'Mock up'[Measure Name] ),
                ALLSELECTED ( 'Mock up'[Measure Name] ),
                 'Mock up'[Score] > 0 
            )
 
)

Metric Imperative = if([Scored] = BLANK(), BLANK(), [Weight imperative]/[Scored] )

For the last column with the averages I created the follwoing meaures:

Score_Average = AVERAGE('Mock up'[Score])

Average Score Imperative = 
IF(ISFILTERED('Mock up'[Imperative]), [Score_Average], AVERAGEX( ALLSELECTED('Mock up'[Imperative]), [Score_Average])

Average Score Theme = 
IF(ISFILTERED('Mock up'[Theme Name]), [Score_Average], AVERAGEX( ALLSELECTED('Mock up'[Theme Name]), [Score_Average])
)

Average Score MEasure = 
IF(ISFILTERED('Mock up'[Measure Name]), [Score_Average], AVERAGEX( ALLSELECTED('Mock up'[Measure Name]), [Score_Average])
)

Check result in attach PBIX.

[Anonymous]

Hi @MFelix 

Yes this achieves most of what i was trying to represent visually. thank you so much.

I copied your measures over, kind of understanding them a bit more than i did before this process so really appreciatte your guidance and support.

I have tweaked slightly for what i need which you can see on the file in the folder. but i think this will help to send the message i want people to hear. 

Thank you again.