How to get certain benchmarking items for 3 years next to each other

[powerJR88]

Hi,

I've created a clustered column chart with benchmark metrics provided by the client for one of the dashboards. The client is looking for a clustered column or bar chart that displays a certain benchmark metric for the years 2021, 2022 and 2023 next to each other based on region. For example: APAC LQ 2021 would be next to EMES LQ 2021 which would be next to NA LQ 2021 which would be next to NA LQ 2021, then it would go to APAC LQ 2022, APAC LQ 2023, APAC Med 2021.... all the way until APAC UQ 2023.

This is what I have so far:

How do I change this to what I have described above the bar chart? Appreciate the help.

[v-rzhou-msft · Accepted solution]

Hi @powerJR88 ,

According to your statement, I think you want result as below.

Here I agree with  lbendlin's reply and I will give you more details with a sample.

Firstly you need a measure name table.

Measure = 
SWITCH (
    MAX ( 'Measure Name'[Measure Name] ),
    "LQ", [Average of Lower Quartile],
    "Med", [Average of Median],
    "UQ", [Average of Upper Quartile]
)

Then create your visual as below. Sort your visual by [Measure Name] in Tooltips.

If you want to DIY the color of column, you can try this code and use conditional formatting.

Color for measure =
SWITCH (
    MAX ( 'Measure Name'[Measure Name] ),
    "LQ", "#002A3A",
    "Med", "#004F71",
    "UQ", "#64CCC9"
)

Result is as below.

Best Regards,
Rico Zhou

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

[lbendlin]

You will need to create a reference table with the names of your measures.  Then add that column and the region column to the X axis, expand the hierarchy, and create a new measure that calculates the value based on the selections.

Please provide sample data that fully covers your issue.