Hi,
I have created an interactive dashboard following your instructions and impressed everyone in my company :), just a few clicks and you can turn data into insight, that is so true.
Then, my boss got more demanding (no surprise) and asked me to add the targets and YOY% in the charts, some thing like attached Picture 1.
However, this is only available when I applied a filter. If I remove it, there will be another columns appeared in the chart (Chart 1 pic) as the result of how my data is set up (Interactive Dashboard.xlsx).
Yet mentioned that i dont know how to add YOY % in these charts.
Appreciate your advice.
Hi Lam Lee
Your source data should not have blank cells. All cells must be filled up.
In you example, fill in the blank cells in the PAYOR column and then refresh your Pivot Table.
You should consider filling up other blank cells as well. Otherwise you will run into this problem again.
Hope this helps.
Sunny
Hi Lam Lee,
Sunny has answered your first question. In answer to your Year on Year calculation, I don't think your data suits a year on year calculation because some of the values are missing, but you can see how to do it in the attached file.
It uses the 'Show Values As' settings. And they are plotted on the secondary axis which is set to not display.
I hope that points you in the right direction.
Mynda
Mynda Treacy said
Hi Lam Lee,Sunny has answered your first question. In answer to your Year on Year calculation, I don't think your data suits a year on year calculation because some of the values are missing, but you can see how to do it in the attached file.
It uses the 'Show Values As' settings. And they are plotted on the secondary axis which is set to not display.
I hope that points you in the right direction.
Mynda
Hi Mynda, thanks for the tip and sample file.
Unfortunately that i need to show my data the other way around (swap column vs. row) which makes it doesnt look that nice as yours anymore, as there is an empty column of FY18 YOY%.
But the tip is great, i can definitely use it for another report.
Lam
H
SunnyKow said
Hi Lam LeeYour source data should not have blank cells. All cells must be filled up.
In you example, fill in the blank cells in the PAYOR column and then refresh your Pivot Table.
You should consider filling up other blank cells as well. Otherwise you will run into this problem again.
Hope this helps.
Sunny
Hi Sunny,
Thanks for the advice.
There is a catch that I should have be more clearer. BP is only available for Local (which includes IB and OB), or IB or OB or L/Weight_Type (both include IB and OB).
For that reason, i cannot just fill in the empty cells. It seems to be a question for data input rather than charting?
Thanks anyway and Merry X'mas.
Hi Lam,
In that case you might be better off with separate charts for Local and Offshore.
Mynda
