Bila buat presentation, 1st thing kena tanya yourself nak analyze apa sebenarnya....
I would like to analyze the utilization of revenue, cost etc against budget and achievement against target
So, from Pivot Table tarik CM actual, 2020T. Contoh tunjuk camna nak tukar sum of Revenue jadi % from Total (i.e proportion revenue by specific region from Total Revenue). Just right click and boleh tukar value ikut suka. Tapi aku takleh buat camtu sebab yang tu reference dia based on 1 column saja i.e revenue. Aku punya nak compare dengan column lagi satu that is 2020T.
Presentation wise, selalu pivot tu tulis Row, Sum of bagai. Kalu nak tukar pegi Pivot Table Tools - Design - Report Layout - Tabular Form. Nanti kuar title 'P&L item' instead of Row.
How to create progress doughnut chart from pivot table
Kena create helper column (1-x%) sebab nanti doughnut chart tu ada 2 axis
- Select 'create doughnut chart' tanpa highlight apa-apa values dulu
- Then right click - Add value. Dalam series, taip 1 (no of occurence) x 20 kali
- Kalo nak adjust doughnut jadi doughnut gebu atau kurus = Format Data series - Doughnut hole size
- Tukar kaler yang bermacam-macam tu to solid fill, then biru (nanti sume seragam jadi kaler biru)
*dalam video, dia copy the chart sekarang untuk create lagi 2 chart yang sama
Create another doughnut dalam chart tu (nanti akan jadi secondary axis). Bila add value, masukkan data % daripada table, in my case achievement % against budget.
- Chart 2 tu ada keliling kat luar chart 1, bila tukar ke secondary axis, nanti dia cover chart 1 (chart 1 tak nampak)
- Now kena modify chart 2 (yg secondary axis tu) untuk reveal chart 1
- Highlight data point (pecahan doughnut yang kita nak format), select No Fill
- Select the other data point (balance budget), yang ni select fill kaler Putih. Then make it transparent
So doughnut chart dah siap
Nak bagi cantik nanti, masukkan label kat tengah doughnut tu. Jangan lupa masukkan slicer
No comments:
Post a Comment