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.

Value:
Entity
Count of LS delivery status
Count of LS review status
Count of FS delivery status
Count of FS review status
N/A
7799
Waiting
54311
Due
45114
Late
11503
Delivered in time
99119
Delivered late
3953

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Watch MrExcel Video

Forum statistics

Threads
1,123,334
Messages
5,601,012
Members
414,421
Latest member
tonybear1994

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