Building and visualizing a scorecard

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.