How to group X-axis in a bar chart?

[rizwanulhasan]

Hi all,

Hope all's well.

In the attached file, purpose of chart with title nRFT Weekly % is to show values of Columns X in blue and Column Y in red.

However, I want the weeks to appear once as similar to the one in chart nRFT Weekly Tasks. Now this is a bit difficult because in such case, the average % value has to be identified for that particular week. I am unsure how to do that.

On the other hand, chart with title nRFT Weekly Tasks is linked to pivot and therefore automatically groups the weeks. Unfortunately i couldn't find a way to display columns % in columns X and Y in pivot.

I just want that the chart with title nRFT Weekly % to show weekly percentage which is calculated by dividing total count of Column V by total count of Column U on weekly basis.

Ex. For Week 1 of Feb, it will be 3/84 = 3.57%

Can someone please help

[JeteMc]

In the attached file a calculated field, Weekly %: ='nRFT Tasks' /'Tasks Closed', is used.
The resulting pivot table chart is shown below the pivot table.
Let us know if you have any questions.

[rizwanulhasan]

Brilliant JeteMc

Just 2 queries.

1. I have the same fields under Axis i.e Column 1 and Week Number, however they appear in same column whereas it shows in different columns in your file like Feb in 1 column and week numbers in 2nd column. May i know how to do that?

Attachment 824910

Attachment 824911


2. Any way that i can set color for bars in graph such that if % is less than 3, bars should be in green else red.


Can you help

[JeteMc]

As to #1: In the Pivot Table Design tab change the Report Layout to Tabular
As to #2: Vary individual data marker colors manually: https://support.microsoft.com/en-us/...e-fed830e45851
Note that there may be a way to produce a regular chart, as opposed to a pivot chart, from the pivot table. The regular chart might have the ability to automatically display the green/red colors, however, that isn't something I know much about.

[MrShorty]

IMO, the definitive tutorial on creating regular charts from pivot data: https://peltiertech.com/regular-char...-pivot-tables/

While you're at it, another tutorial from Peltier to show conditional formatting of charts: https://peltiertech.com/conditional-...-excel-charts/ Note that most of the work is in the spreadsheet arranging the data into columns according to your desired conditions. The first step in getting points conditionally formatted in the chart, then, would be to extract the points from the pivot table into another part of the spreadsheet according to conditions. Then build the chart from that table.

[rizwanulhasan]

@JeteMc

Many thanks again.

#1 Perfect

#2 I referred the link you shared and tried vary colors by point option and selected red color. However, all bars appear red. How did you set bar colors based on percentage

Sorry, but may i also know how did you get Sum of weekly % in Values field?


@MrShorty

Thanks for your response

[JeteMc]

I didn't set bar colors based on percentage. I individually selected each bar and then set the color for that one bar.
Sum of weekly % is a calculated field.
Here is an article that explains how to make/use a calculated field for a pivot table: https://www.got-it.ai/solutions/exce...in-pivot-table
Let us know if you have any questions.

[rizwanulhasan]

May i please know what formula to be added in Calculated Field?

Attachment 825006

[JeteMc]

As stated in post #2, the formula is: ='nRFT Tasks' /'Tasks Closed'
In the file attached to post #4, select the dropdown in the Name window and then select Weekly % and the formula should be displayed in the Formula window.
Let us know if you have any questions.

[rizwanulhasan]

Thanks a ton for answering all queries JeteMc. Really appreciate your kind response each time.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.