Forum Discussion
Design scorecard matrix
Hi everyone,
I need help with creating a scorecard in PBI with this structure, as you can see each row is a different metric with it's own value, format, goal, conditional formatting, etc. but all of them are sharing a horitzontal axis for the weeks.
These are some of the challenges I've faced so far:
- Matrix Visual cannot display weeks in columns for different metrics without affecting the structure
- I tried creating a table single column for each KPI value and Name, then formatted it depending if it's percentage, currency, etc. BUT you can't apply individual targets/goals to each KPI if you're using only one column so it doesn't work
- I could create each metric individually, then grouping together all of them in one object so it can replicate a single scorecard visual BUT then each metric has it's own navigation bar/scroll bar at the bottom, so you need to move one by one to look across previous or future weeks, not really intuitive or easy to use right?
- The Power KPI Matrix (custom visual) doesn't let you put the weeks in columns as the example in the screenshot, and personally I don't really like it, but if you know there is another visual to do the same, that'll work
Here is a onedrive link to download a dummy pbix with the data models and ideas https://1drv.ms/u/s!AqyfrLljieLpjwd9Hqd5_Fww-CA1?e=UgIM0X
Any idea that could potentially solve this is more than welcome, thanks!
krist_pbi , See if the attached file can help. You can have the additional conditions for conditional formatting
5 Replies
- HoangHugoSolution Specialist
Hi, you can, please refer model below
measue
PBI =
var select_metric = SELECTVALUED (KPI)
return
SWITCH (select_metric,
"Metric 1", FORMAT (your formula for Metric 1, "General Number"),"Metric 2", FORMAT (your formula for Metric 2, "Percent"),
"Metric 3", FORMAT (your formula for Metric 3, "Currency"),
"Metric 4", FORMAT (your formula for Metric 4, "General Number").
Blank ())
- krist_pbiHelper I
Hey, thanks man, sorry I'm not that good in DAX yet, I kinda understand the formatting but my data model has different facts tables so that makes it harder to get it right
I have this file with dummy data with the current data model I'm working with, maybe this can give you a better perspective of what I need https://1drv.ms/u/s!AqyfrLljieLpjwd9Hqd5_Fww-CA1?e=UgIM0X that is the onedrive link for the pbix file
I appreciate your help!
- amitchandakSuper User
krist_pbi , Please refer to the suggestion made by HoangHugo first.
I would like to explore the option - Switch Values or Row / Show on Row in Matrix visual
Nothing on row and week/date on column (pivot)
And use conditional formatting if needed based on measure
How to do conditional formatting by measure and apply it to pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pane-66e0afcb15f3- krist_pbiHelper I
Hey, thanks man, sorry I'm not that good in DAX yet and my data model has different facts tables so that makes it harder to get it right
I have this file with dummy data with the current data model I'm working with, maybe this can give you a better perspective of what I need https://1drv.ms/u/s!AqyfrLljieLpjwd9Hqd5_Fww-CA1?e=UgIM0X that is the onedrive link for the pbix file
I appreciate your help!- amitchandakSuper User
krist_pbi , See if the attached file can help. You can have the additional conditions for conditional formatting