Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Background:
As mentioned above, there is a growing need to carry weighting for more informing metrics.
I have attached the following to help visualise
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
Excel - Weighting and metric / contract examples
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |