Excel Pivot table status dashboard - separate grouped columns

MaartenBurg

New Member
Joined
Dec 13, 2016
Messages
1
Hello,

I am trying to make a dashboard for one of my collegues which will determine if so called 'LS' and 'FS' deliverables are reviewed and delivered in time. Those deliverables have to be made for about 30 different customers.

I already made the sheet where they fill in the due date and when they've actually delivered. A formula determines what the status is. This can be: N/A, Waiting, Due, Late, Delivered in time, Delivered late.
The first column contains the name of the unique customer.

What I want to make is a pivot table that shows the count of the status for each deliverable, separated for the four deliverables, in one pivot chart.

Basically if I put the 'LS delivery status' in column, Count of entity in values, it shows what I want to see, but I want to see this graph for all statusses in one chart. I want to keep it a pivot chart so that my collegue can drill-down on the data by double-clicking.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Value:
Entity
[/TD]
[TD]Count of LS delivery status
[/TD]
[TD]Count of LS review status
[/TD]
[TD]Count of FS delivery status
[/TD]
[TD]Count of FS review status
[/TD]
[/TR]
[TR]
[TD]N/A
[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Waiting
[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Due
[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]11[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Late
[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Delivered in time
[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Delivered late
[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Problem is that when I just put it in a pivottable, it groups up the different statusses. For instance: Delivered in Time-Due-Waiting- Waiting or Delivered late - Late - Waiting - N/A and counts these as unique.
This messes up my data and makes me unable to show what I want.

I have an example file, but couldn't upload it here so I put it on DropBox: https://www.dropbox.com/s/rkie0ut7dsgkcsu/Example file.xlsx?dl=0

I have tried searching the internet for a solution but did not find my answer. Sorry for the wall of text but I hope I made my problem clear. I am using Excel 2016.

If anyone can help me out, it is very much appreciated!!!

Thanks.
Maarten
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Forum statistics

Threads
1,223,275
Messages
6,171,127
Members
452,381
Latest member
Nova88

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
Back
Top