Use dynamic measures to replicate Excel bar chart

[Meep]

Hi everyone,

New to Power BI here and I am stuck with this chart. 

I want to replicate this win rate chart that I had created in Excel:

	Oct 2022	Oct 2021
Overall Win Rate	15.0%	16.0%
Supplier 1 Win Rate	8.2%	9.3%
Supplier 2 Win Rate	21.2%	12.7%
Supplier 3 Win Rate	40.3%	25.4%

I created the measures to calculate all the percentages in Power Bi, but I cannot replicate the chart design because for some reason they are stuck to each other: 

I was thinking about creating a table from the measures, but they are no longer dynamic which means the chart will be useless. Any idea how i could separate the columns to make it look like the ones in Excel?

Thank you.

[v-yanjiang-msft · Accepted solution]

Hi @Meep ,

According to your description, here's my solution.

1.Create a new table. Don't make relationship between the two tables.

2.Create a measure. In the code, [Overall Win Rate], [Supplier 1 Win Rate] etc. are all measures.

Measure =
SWITCH (
    MAX ( 'Category'[Category] ),
    "Overall Win Rate", [Overall Win Rate],
    "Supplier 1 Win Rate", [Supplier 1 Win Rate],
    "Supplier 2 Win Rate", [Supplier 2 Win Rate],
    "Supplier 3 Win Rate", [Supplier 3 Win Rate]
)

Put the new Category column in X-axis, measure in Y-axis and Year-Month column in Legend, get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[Meep]

Hi @v-yanjiang-msft,

Thank you very much for the help. I replicated your work and even got the columns to be in the correct order by labelling the values in the Category column as A B C D. I also got it to display the rates in the correct order (2022 first instead of 2021) by creating a dummy table containing A B C D and 1 2 3 4. I then created a relationship between Category table and Dummy table and instead of using Category in X axis, I used the column in the Dummy table containing the 1 2 3 4 values. 

Very neat trick and thank you for the help!

[Meep]

Hi @v-yanjiang-msft ,

Thank you for the help. I will give it a try this morning. Do you know if those measures in the table will stay dynamic and change accordingly when I switch the filter applied to the measures or if new data comes in?

Thank you

[amitchandak]

@Meep , if these 4 are measures then you can create calculation groups and use that on axis

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0