Suggestions/ideas for showing data in a fair way

[RichOB]

Hi, I have 5 dog fostering kennels and the table below has:

 - The number of dogs that have stayed in the kennel.

 - The number of dogs that have moved into a new home.

I need to present these details to the donors and need a fair way to display the data.

Although correct, I feel like showing the below as percents in a new column is quite misleading as Manchester only has 4 kennel spaces = 100% success rate, while Newcastle has had 40 dogs stay in the kennels and 30 of them have moved into new homes, which is arguably a taller achievement. How would you show the kennels with more dogs staying and having a lesser amount moved into a new home fairly?

Kennel_Location	Number_of_Dogs_Stayed	Dogs_Moved_to New Home
Manchester	4	4
Newcastle	40	30
Glasgow	12	4
Edinburgh	20	14
Bristol	50	12

Thanks

[DataNinja777 · Accepted solution]

Hi @RichOB ,

Here is the DAX code for calculating the Weighted Success Rate and Impact Score measures in Power BI.

Weighted Success Rate = 
VAR SuccessRate = DIVIDE(SUM('Kennel Data'[Dogs_Moved_to_New_Home]), SUM('Kennel Data'[Number_of_Dogs_Stayed]), 0)
VAR LogWeight = LOG(SUM('Kennel Data'[Number_of_Dogs_Stayed]), 10)
RETURN SuccessRate * LogWeight

Impact Score = 
SUM('Kennel Data'[Dogs_Moved_to_New_Home]) * [Weighted Success Rate]

The Weighted Success Rate measure calculates the success rate by dividing the number of dogs moved to new homes by the number of dogs stayed, and it applies a logarithmic weight based on the kennel size. The Impact Score measure multiplies the number of dogs moved to new homes by the weighted success rate to give a fair representation of the kennel’s performance. Use these measures in your Power BI visual to display the data fairly.

Best regards,

[BA_Pete · Accepted solution]

Hi @RichOB ,

A few options off the top of my head:

-1- Just do the percentage and leave both the numerator and denominator values visible to allow the end user to make the link themselves (may over-clutter the report depending on your requirements).

-2- Create a new ratio of sorts that 'weights' any outputs by the number of dogs stayed (would need to be created/agreed with stakeholders and report annotated to explain to new users).

-3- Calculate the percentage for each kennel against the TOTAL number of stays across all kennels, to show performance as a metric of overall contribution, rather than just site performance.

Pete

[v-xianjtan-msft · Accepted solution]

Hi @RichOB 

Thanks to DataNinja777 and BA_Pete for their attention to this thread and their great advice. Please consider accepting their replies as a solution if it helps you.

Allow me to add a possible solution here, which is to consider using standardized scoring. Standardized scoring converts data to the same scale, allowing performance across kennels to be more fairly compared.

Create the following measures:

Percentage of moves = DIVIDE(SUM('Kennel Data'[Dogs_Moved_to New Home]), SUM('Kennel Data'[Number_of_Dogs_Stayed]))

Average percentage of moves = AVERAGEX(ALL('Kennel Data'), [Percentage of moves])

Standard deviation of the proportion of moves = STDEVX.P(ALL('Kennel Data'), [Percentage of moves])

Standardized scores = DIVIDE(([Percentage of moves] - [Average percentage of moves]), [Standard deviation of the proportion of moves])

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete · Accepted solution]

Hi @RichOB ,

A few options off the top of my head:

-1- Just do the percentage and leave both the numerator and denominator values visible to allow the end user to make the link themselves (may over-clutter the report depending on your requirements).

-2- Create a new ratio of sorts that 'weights' any outputs by the number of dogs stayed (would need to be created/agreed with stakeholders and report annotated to explain to new users).

-3- Calculate the percentage for each kennel against the TOTAL number of stays across all kennels, to show performance as a metric of overall contribution, rather than just site performance.

Pete

[DataNinja777 · Accepted solution]

Hi @RichOB ,

Here is the DAX code for calculating the Weighted Success Rate and Impact Score measures in Power BI.

Weighted Success Rate = 
VAR SuccessRate = DIVIDE(SUM('Kennel Data'[Dogs_Moved_to_New_Home]), SUM('Kennel Data'[Number_of_Dogs_Stayed]), 0)
VAR LogWeight = LOG(SUM('Kennel Data'[Number_of_Dogs_Stayed]), 10)
RETURN SuccessRate * LogWeight

Impact Score = 
SUM('Kennel Data'[Dogs_Moved_to_New_Home]) * [Weighted Success Rate]

The Weighted Success Rate measure calculates the success rate by dividing the number of dogs moved to new homes by the number of dogs stayed, and it applies a logarithmic weight based on the kennel size. The Impact Score measure multiplies the number of dogs moved to new homes by the weighted success rate to give a fair representation of the kennel’s performance. Use these measures in your Power BI visual to display the data fairly.

Best regards,