Clustered Column bar charts summing up to match Cards.

[Anonymous]

Hello Community,

I have an Audit Actions report that I am working on at the moment. Below is how visuals look like another 3 reports and  this is a copy of one of those. I am using Clustered column chart.

However, the 0 > 29 Days Overdue, 30 > 59 Days Overdue and 0 > 60+ Days Overdue at the top is right and should be as is and the Legend is right as well.

Requirement is to have 0 > 29 Days Overdue, 30 > 59 Days Overdue and 0 > 60+ Days Overdue on the X-axis replacing Open, Closed and Ready to inspect respectively showing groups of bars as above for Discipline, Design, Environmental, Quality & Safety if the data is available and the numbers of the groups of bars should match the number above in the tiles such as 24, 18 and 49.

It should look like below, of course, the number of bars should add up to the numbers on top:

Can anyone please let me know how to attain this? Or should I be using some other visuals to attain this. Not sure if unpivoting does any good either!

This model was created by someone else and I am taking over. Some of the other things I am considering are to remodel with a Star Schema and have a proper Date Calendar as well. Open to any other suggestions too.

Your help is greatly appreciated.

Kind regards,

Bhoga

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

You could try to create new group to change the name.

Best Regards,

Xue Ding

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

[mattneil · Accepted solution]

Hi Bhoga,

I'd create a calculated column to categorise these into your "buckets" of overdue ranges.

[Overdue Range] = IF([Days Overdue] >= 60, "60+ Days Overdue",

                                  IF([Days Overdue] > 30, "30 > 59 Days Overdue",

                                  IF([Days Overdue] > 0, "0 > 29 Days Overdue")))

It will return blank for any records that are not overdue, you could always include "Not Overdue" in the final ELSE statement and then filter this out in the report.

Bonus here is that the X axis should sort in the order you want due to the leading digits on those ranges as well.

Hope that helps

Cheers,

Matt

Principal Consultant

Lime Theory

Find me on LinkedIn

[Anonymous]

Hi Matt,

Thanks a million for your quick response. Yes, your solution works. Your time is greatly appreciated.

Kind regards,

Bhoga

(giftedbrain)

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

You could try to create new group to change the name.

Best Regards,

Xue Ding

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

[Anonymous]

Hi Xue Ding,

Thanks a million for your quick response. Yes, your solution works. Your time is greatly appreciated.

Kind regards,

Bhoga

(giftedbrain)