cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ashwani_singh1 Frequent Visitor
Frequent Visitor

Clustered column chart -percentage

Hi ,

 

How we can create a desired Chart  in power bi  from the below data:-

data in this chart will be dynamic , it will change according to filter of date & year.

sample chart.jpg

Data:--

YearWeekMonthSection-1StatusSection-2StatusSection-3StatusSection-4Status
2019Week 6Feb ICR ICR ICR ICR
2019Week 6Feb CR CR CR ICR
2019Week 6Feb ICR ICR ICR ICR
2019Week 7Feb CR CR ICR ICR
2019Week 7Feb ICR ICR ICR ICR
2019Week 7Feb CR ICR ICR ICR
2019Week 7Feb PD ICR PD ICR
2019Week 7Feb ICR ICR ICR ICR
2019Week 8Feb CR ICR CR ICR
2019Week 8Feb CR ICR CR CR
2019Week 8Feb CR ICR CR CR
2019Week 8Feb ICR ICR CR ICR
2019Week 8Feb ICR ICR CR ICR
2019Week 9Feb CR ICR CR CR
2019Week 9Feb PD PD CR PD
2019Week 9Feb ICR ICR CR PD
2019Week 9Feb CR CR CR PD
2019Week 9Feb PD PD CR PD
2019Week 9Feb ICR ICR ICR PD
2019Week 9Feb CR CR CR CR
2019Week 9Feb PD PD PD PD

 

 
 
  •  
1 ACCEPTED SOLUTION

Accepted Solutions
dm-p Member
Member

Re: Clustered column chart -percentage

Hi @ashwani_singh1 and welcome aboard.

This looks like a question that can be solved with core visuals. As such, you will get a much faster response (probably within minutes) in a more frequented location like the Desktop forums for questions of this manner. They will also be able to give you better advice on how to do this using measures, as well and any specific formatting questions, but I'm confident that this will get you started.

Firstly, your data is not in quite the correct format as pasted. the Section-x columns are empty, and the status has its own separate column. If you can modify your source data a little bit then I would suggest that each status coumn has a Section-x heading and then we can transform it later. I'm going to assume that the structure can be changed to this:

YearWeekMonthSection 1Section 2Section 3Section 4
2019Week 6FebICRICRICRICR
2019Week 6FebCRCRCRICR
.....................

We'll use the query editor to make this fit your visual requirements. After import, we should have something like this:

image.pngTable after import

Now, select the first three columns, right-click and select Unpivot Other Columns from the context menu, e.g.:

image.pngUnpivot Other Columns

You'll now have a tall table, with and Attribute and a Value column, e.g.:

image.pngTable After Unpivot

You now have columns that will work as attributes in Power BI when you want to build your chart. We'll rename them to something more sensible for your use case (Section and Status), e.g.:

image.pngRenamed Columns

Now, Close & Apply the query changes to be taken back to the Report view.

First, we'll put your filters in. Add two slicers to the canvas, and add Year to one and Week to the other, e.g.:

image.pngSlicers Added to Canvas

You can add slicers for whatever fields you need - these two are just for illustrative purposes.

Now, add a Clustered Column Chart to the canvas.

  • Add the Section field from your data model to the Axis container on the chart.
  • Add Status to the Legend container.
  • Add Status to the Value container.

Our chart now looks like below. Note that the Status attribute changes to show Count of Status. This is because it's behaving as a measure for the chart. This doesn't quite fulfil your requirements, but we'll get to that in a second.

image.pngClustered Column Chart with Count

Power BI lets us convert this value to a % of grand total rather than the count, but this is for the whole visible data set, and it looks like you'll want to get this total for each Section value. We'll need a measure for this that takes this into consideration.

  • Right-click the table and select Add Measure
  • Paste the following DAX code into the text box:
    Section % = 
        DIVIDE(
            COUNT('Table1'[Status]),
            CALCULATE(
                COUNT(Table1[Status]),
                ALLSELECTED(Table1[Status])
            )
        )
    This will create a measure called Section %. This divides the value in each bar by the total for each section group. I would suggest that for more help on these kinds of areas, ask in the DAX forum, as there's some awesome people in there who can explain much better than I can Smiley Happy

  • In the modelling ribbon, change your measure to a percentage format.
  • Replace the field in the Value container with this new measure, e.g.:
    image.pngChart With %

All that's left is your data labels:

  • Click on the Format pane, and switch Data Labels on.
  • Expand and change Value decimal places to 0.

And we're done!

image.pngComplete Chart

Selecting the slicers will modify the figures in the chart as needed, as per your requirements.

Hopefully this shows you what you need is possible.

Good luck!

Daniel

Did I answer your question? Mark my post as a solution!

2 REPLIES 2
dm-p Member
Member

Re: Clustered column chart -percentage

Hi @ashwani_singh1 and welcome aboard.

This looks like a question that can be solved with core visuals. As such, you will get a much faster response (probably within minutes) in a more frequented location like the Desktop forums for questions of this manner. They will also be able to give you better advice on how to do this using measures, as well and any specific formatting questions, but I'm confident that this will get you started.

Firstly, your data is not in quite the correct format as pasted. the Section-x columns are empty, and the status has its own separate column. If you can modify your source data a little bit then I would suggest that each status coumn has a Section-x heading and then we can transform it later. I'm going to assume that the structure can be changed to this:

YearWeekMonthSection 1Section 2Section 3Section 4
2019Week 6FebICRICRICRICR
2019Week 6FebCRCRCRICR
.....................

We'll use the query editor to make this fit your visual requirements. After import, we should have something like this:

image.pngTable after import

Now, select the first three columns, right-click and select Unpivot Other Columns from the context menu, e.g.:

image.pngUnpivot Other Columns

You'll now have a tall table, with and Attribute and a Value column, e.g.:

image.pngTable After Unpivot

You now have columns that will work as attributes in Power BI when you want to build your chart. We'll rename them to something more sensible for your use case (Section and Status), e.g.:

image.pngRenamed Columns

Now, Close & Apply the query changes to be taken back to the Report view.

First, we'll put your filters in. Add two slicers to the canvas, and add Year to one and Week to the other, e.g.:

image.pngSlicers Added to Canvas

You can add slicers for whatever fields you need - these two are just for illustrative purposes.

Now, add a Clustered Column Chart to the canvas.

  • Add the Section field from your data model to the Axis container on the chart.
  • Add Status to the Legend container.
  • Add Status to the Value container.

Our chart now looks like below. Note that the Status attribute changes to show Count of Status. This is because it's behaving as a measure for the chart. This doesn't quite fulfil your requirements, but we'll get to that in a second.

image.pngClustered Column Chart with Count

Power BI lets us convert this value to a % of grand total rather than the count, but this is for the whole visible data set, and it looks like you'll want to get this total for each Section value. We'll need a measure for this that takes this into consideration.

  • Right-click the table and select Add Measure
  • Paste the following DAX code into the text box:
    Section % = 
        DIVIDE(
            COUNT('Table1'[Status]),
            CALCULATE(
                COUNT(Table1[Status]),
                ALLSELECTED(Table1[Status])
            )
        )
    This will create a measure called Section %. This divides the value in each bar by the total for each section group. I would suggest that for more help on these kinds of areas, ask in the DAX forum, as there's some awesome people in there who can explain much better than I can Smiley Happy

  • In the modelling ribbon, change your measure to a percentage format.
  • Replace the field in the Value container with this new measure, e.g.:
    image.pngChart With %

All that's left is your data labels:

  • Click on the Format pane, and switch Data Labels on.
  • Expand and change Value decimal places to 0.

And we're done!

image.pngComplete Chart

Selecting the slicers will modify the figures in the chart as needed, as per your requirements.

Hopefully this shows you what you need is possible.

Good luck!

Daniel

Did I answer your question? Mark my post as a solution!

ashwani_singh1 Frequent Visitor
Frequent Visitor

Re: Clustered column chart -percentage

Thanks for your support.

It works perfectly.