Pivot table error bar chart help-use power query or power pivot

zh_m

New Member
Joined
Aug 18, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi

Please see the attached file link below. Basically I want to achieve the image of the chart below in my file with the error bars. In my file, Instead of years, I have a month column and aban rate column(dummy file). What I want the pivot chart to do is in the following steps below and would appreciate your help in achieving it:

1. give the variance from current to the next month in power query or power pivot so it is automated
2. if for example the ab rate is 6.22% October 2019 and 3.07% in November 2019 than I want it to give a green arrow with variance(its getting better when its lower). Else, if the aban rate is 6.91% in December2020 and goes up to 7.32% in January then I want it to show a red with variance(.
3. Dynamic pivot chart since new month data will be appended each month so don't want format to lose as well.


The image below is just an example of the error bar chart that I want to have on my chart in the file attached with my data in pivot table.

File link below:

variance or error bars.xlsx
 

Attachments

  • Capture9.PNG
    Capture9.PNG
    71.7 KB · Views: 3

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Watch MrExcel Video

Forum statistics

Threads
1,123,343
Messages
5,601,073
Members
414,426
Latest member
fraru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top