Clustered column chart -percentage

[Anonymous]

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.

Data:--

Year	Week	Month	Section-1	Status	Section-2	Status	Section-3	Status	Section-4	Status
2019	Week 6	Feb		ICR		ICR		ICR		ICR
2019	Week 6	Feb		CR		CR		CR		ICR
2019	Week 6	Feb		ICR		ICR		ICR		ICR
2019	Week 7	Feb		CR		CR		ICR		ICR
2019	Week 7	Feb		ICR		ICR		ICR		ICR
2019	Week 7	Feb		CR		ICR		ICR		ICR
2019	Week 7	Feb		PD		ICR		PD		ICR
2019	Week 7	Feb		ICR		ICR		ICR		ICR
2019	Week 8	Feb		CR		ICR		CR		ICR
2019	Week 8	Feb		CR		ICR		CR		CR
2019	Week 8	Feb		CR		ICR		CR		CR
2019	Week 8	Feb		ICR		ICR		CR		ICR
2019	Week 8	Feb		ICR		ICR		CR		ICR
2019	Week 9	Feb		CR		ICR		CR		CR
2019	Week 9	Feb		PD		PD		CR		PD
2019	Week 9	Feb		ICR		ICR		CR		PD
2019	Week 9	Feb		CR		CR		CR		PD
2019	Week 9	Feb		PD		PD		CR		PD
2019	Week 9	Feb		ICR		ICR		ICR		PD
2019	Week 9	Feb		CR		CR		CR		CR
2019	Week 9	Feb		PD		PD		PD		PD

Delete

[dm-p · Accepted solution]

Hi @Anonymous 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:

Year	Week	Month	Section 1	Section 2	Section 3	Section 4
2019	Week 6	Feb	ICR	ICR	ICR	ICR
2019	Week 6	Feb	CR	CR	CR	ICR
...	...	...	...	...	...	...

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

Table after import

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

Unpivot Other Columns

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

Table 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.:

Renamed 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.:

Slicers 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.

Clustered 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 🙂
  
  
• In the modelling ribbon, change your measure to a percentage format.
• Replace the field in the Value container with this new measure, e.g.:
  Chart 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!

Complete 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!

[Anonymous]

Thanks for your support.

It works perfectly.