Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Use dynamic measures to replicate Excel bar chart

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 2022Oct 2021
Overall Win Rate15.0%16.0%
Supplier 1 Win Rate8.2%9.3%
Supplier 2 Win Rate21.2%12.7%
Supplier 3 Win Rate40.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.

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi Anonymous ,

    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.

4 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi Anonymous ,

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , 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