cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

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

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

hierarchy 1.jpg

  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

 

Scores.jpg

 

Example scores

Metrics.jpg

 

Scores within powerBI using a measure to aggregate by category

 

Oneill1_0-1610206412316.png

 

Power BI mockup 

Excel - Weighting and metric / contract examples 

Full Folder 

 

 

1 ACCEPTED SOLUTION
Super User III
Super User III

Hi @Oneill1 ,

 

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.

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

2 REPLIES 2
Super User III
Super User III

Hi @Oneill1 ,

 

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.

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors