Hi,
I’m trying to create a Plan vs Actual Chart (Cluster and Stacked) in Excel where:
Offer Count is based on Offer Date → should appear as a clustered column
Inspection Clearance is based on actual Inspection Date → should appear as a stacked column with Count of orders cleared.
Pending orders should also appear in the stack, but must stay tied to the original Offer Date (no carry-forward)
Everything should be plotted on one continuous Date X-axis
My challenge:
Offer Date and Inspection Date are different, so I need two date-based aggregations on the same X-axis (cluster + stacked), and I’m not able to get Excel to combine them correctly.
I’m also facing difficulty building the correct summary table/data model for this visualization.
I have also attached excel where I built some charts and table. Please go through it and mentioned clearly what I exactly want!
Would really appreciate guidance on:
1. How to structure the data
2. How to build the correct summary table
3. The right chart type/settings to combine these two series
4. Any Power Pivot/Power Query approach if needed
Thanks in advance!
Sourabh Bellad