Design scorecard matrix

[krist_pbi]

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!

[amitchandak]

@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/...
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pan...

[krist_pbi]

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!

[amitchandak · Accepted solution]

@krist_pbi , See if the attached file can help. You can have the additional conditions for conditional formatting

[HoangHugo]

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_pbi]

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!