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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,682
Messages
5,838,787
Members
430,569
Latest member
sbardelli

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