Forum Discussion
Building and visualizing a scorecard
We have a social media performance scorecard that we're calculating and tracking via Google Sheet, which I am looking at transposing into PowerBI for automation.
Basically, there are five key metrics: followers, impressions, engagement, link clicks and leads with their assigned weights equalling 100%.
Brand 1 would have different performance scorecard than Brand 2 where, for example, getting less 200 new followers in a month equals a score of 1. For Brand 2, it would be less than 100 getting a score of 1.
Then the score per metric will be added to get the overall score for the month.
How do I automatically calculate this on PowerBI given the different targets per brand and weights per metric?
Attach is a sample of the dataset.
2023 Targets | ||||||||
| PAGE | WEIGHT | 1 Poor Brand Health | 2 Unsatisfactory Health | 3 Satisfactory Health | 4 Good Brand Health | 5 Excellent Brand Health | ||
Brand 1 | Page Followers | 5% | < 200 | 201 - 900 | 901 - 1,200 | 1,201 - 1,900 | ≥ 1,901 | |
| Impressions | 20% | < 500,000 | 500,001 - 1,500,000 | 1,500,001 - 2,000,000 | 2,000,001 - 2,750,000 | ≥ 2,750,001 | ||
| Engagement | 25% | < 90,000 | 90,001 - 120,000 | 120,001 - 165,000 | 165,001 - 210,000 | ≥ 210,001 | ||
| Link Clicks | 25% | < 2,500 | < 2,501 - 4,000 | 4,001 - 5,800 | 5,801 - 7,600 | ≥ 7,601 | ||
| Leads | 25% | < 1 | 2 - 5 | 6 - 10 | 11- 20 | ≥ 21 | ||
Brand 2 | Page Followers | 5% | < 100 | 101 - 900 | 901 - 1,200 | 1,201 - 1,900 | ≥ 1,901 | |
| Impressions | 20% | < 600,000 | 600,001 - 1,500,000 | 1,500,001 - 2,000,000 | 2,000,001 - 2,750,000 | ≥ 2,750,000 | ||
| Engagement | 25% | < 100,000 | 100,001 - 120,000 | 120,001 - 165,000 | 165,001 - 210,000 | ≥ 210,001 | ||
| Link Clicks | 25% | < 1,000 | 1,001 - 4,000 | 4,001 - 5,800 | 5,801 - 7,600 | ≥ 7,601 | ||
| Leads | 25% | < 1 | 2 - 5 | 6 - 10 | 10 - 25 | ≥ 26 | ||
Any guidance would be greatly appreciated.
3 Replies
Your dataset format should be transformed for easier computation. An ideal structure could be:
| Brand | Metric | Weight | Score_1 | Score_2 | Score_3 | Score_4 | Score_5 | Threshold_1 | Threshold_2 | Threshold_3 | Threshold_4 |
|-------|-------------|--------|---------|---------|---------|---------|---------|-------------|-------------|-------------|-------------|
| Brand1| Followers | 5% | ... | ... | ... | ... | ... | ... | ... | ... | ... |And then start your journey with DAX:
Followers Score Brand 1 = VAR CurrentFollowers = [Your followers column for Brand 1] RETURN IF( CurrentFollowers <= LOOKUPVALUE(Targets[Threshold_1], Targets[Metric], "Followers", Targets[Brand], "Brand1"), LOOKUPVALUE(Targets[Score_1], Targets[Metric], "Followers", Targets[Brand], "Brand1"), IF( CurrentFollowers <= LOOKUPVALUE(Targets[Threshold_2], Targets[Metric], "Followers", Targets[Brand], "Brand1"), LOOKUPVALUE(Targets[Score_2], Targets[Metric], "Followers", Targets[Brand], "Brand1"), ... [Continue this nested IF for all your thresholds] ) )Repeat this measure creation for each metric for each brand. You may want to create a generalized measure if possible using SWITCH or other functions to avoid repetitive code.
Then you can use a score card to display individual scores for each metric and an overall score.
What I recommend ? Instead of hardcoding values like metric names or brands, use Power BI’s parameter and query functionalities.
Use Drillthrough to allow users to click on a particular metric and see a detailed breakdown or historical data.
Thank you. Will try it out and revert soonest.
