Pivot Table alignment for creating Pivot chart

Joined
May 27, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I have set of raw data from which I need to create chart for Received vs Delivered by considering Division in Filter Field. But I am not getting correct values. Please help me to sort out this. I have attached the raw data and the table currently I am getting for reference.

Raw Data:

Raw data.PNG


Pivot table that is getting currently.

Pivottable.PNG


Required Output is
 

Attachments

  • Final Pivot.PNG
    Final Pivot.PNG
    4.4 KB · Views: 3

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,285
Office Version
  1. 365
Platform
  1. Windows
Ideally you want to address this at the source, it is unlikely that the data comes this way without it being manipulated. Get them to give it to you in its more natural state.

Its pretty easy to convert this using Power Query. Either in a summarised form or if you "Unpivot" it, it would become Pivot Table Friendly.

Alternatively you could just use formulas.

The formulas are already there, I called the Table "tblData"

If either of the Power Query options are of interest, I can give you either the code or the steps to get there, just let me know.

20210528 Transpose for Chart.xlsx
ABCDEFGHIJKLMNOPQ
1
2Original DataFormula Version
3
4DivisionReceivedDeliveredJanFebMarAprMay
5AJanJanReceived97000
6AJanJanDelivered59200
7AJanJan
8AJanFebPower Query Summary Version
9AJanFeb
10AFebFebAttributeJanFebMar
11BJanJanReceived970
12BJanJanDelivered592
13BFebFeb
14BFebFeb
15BFebMar
16CJanFeb
17CJanFebPower Query Unpivot VersionPivot Based on Unpivot PQ
18CFebFeb
19CFebFebDivisionStatusMonthCount of StatusMonth
20CFebMarAReceivedJanStatusDivisionJanFebMarGrand Total
21ADeliveredJanDeliveredA336
22AReceivedJanB2215
23ADeliveredJanC415
24AReceivedJanDelivered Total59216
25ADeliveredJanReceivedA516
26AReceivedJanB235
27ADeliveredFebC235
28AReceivedJanReceived Total9716
29ADeliveredFebGrand Total1416232
30AReceivedFeb
31ADeliveredFeb
32BReceivedJan
33BDeliveredJan
34BReceivedJan
35BDeliveredJan
36BReceivedFeb
37BDeliveredFeb
38BReceivedFeb
39BDeliveredFeb
40BReceivedFeb
41BDeliveredMar
42CReceivedJan
43CDeliveredFeb
44CReceivedJan
45CDeliveredFeb
46CReceivedFeb
47CDeliveredFeb
48CReceivedFeb
49CDeliveredFeb
50CReceivedFeb
51CDeliveredMar
52
Sheet1
Cell Formulas
RangeFormula
G5:K5G5=COUNTIFS(tblData[Received],G$4)
G6:K6G6=COUNTIFS(tblData[Delivered],G$4)
 
Solution
Joined
May 27, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Ideally you want to address this at the source, it is unlikely that the data comes this way without it being manipulated. Get them to give it to you in its more natural state.

Its pretty easy to convert this using Power Query. Either in a summarised form or if you "Unpivot" it, it would become Pivot Table Friendly.

Alternatively you could just use formulas.

The formulas are already there, I called the Table "tblData"

If either of the Power Query options are of interest, I can give you either the code or the steps to get there, just let me know.

20210528 Transpose for Chart.xlsx
ABCDEFGHIJKLMNOPQ
1
2Original DataFormula Version
3
4DivisionReceivedDeliveredJanFebMarAprMay
5AJanJanReceived97000
6AJanJanDelivered59200
7AJanJan
8AJanFebPower Query Summary Version
9AJanFeb
10AFebFebAttributeJanFebMar
11BJanJanReceived970
12BJanJanDelivered592
13BFebFeb
14BFebFeb
15BFebMar
16CJanFeb
17CJanFebPower Query Unpivot VersionPivot Based on Unpivot PQ
18CFebFeb
19CFebFebDivisionStatusMonthCount of StatusMonth
20CFebMarAReceivedJanStatusDivisionJanFebMarGrand Total
21ADeliveredJanDeliveredA336
22AReceivedJanB2215
23ADeliveredJanC415
24AReceivedJanDelivered Total59216
25ADeliveredJanReceivedA516
26AReceivedJanB235
27ADeliveredFebC235
28AReceivedJanReceived Total9716
29ADeliveredFebGrand Total1416232
30AReceivedFeb
31ADeliveredFeb
32BReceivedJan
33BDeliveredJan
34BReceivedJan
35BDeliveredJan
36BReceivedFeb
37BDeliveredFeb
38BReceivedFeb
39BDeliveredFeb
40BReceivedFeb
41BDeliveredMar
42CReceivedJan
43CDeliveredFeb
44CReceivedJan
45CDeliveredFeb
46CReceivedFeb
47CDeliveredFeb
48CReceivedFeb
49CDeliveredFeb
50CReceivedFeb
51CDeliveredMar
52
Sheet1
Cell Formulas
RangeFormula
G5:K5G5=COUNTIFS(tblData[Received],G$4)
G6:K6G6=COUNTIFS(tblData[Delivered],G$4)
Thanks Alex your idea worked out to achieve my output. Great!!
 

Forum statistics

Threads
1,148,191
Messages
5,745,251
Members
423,939
Latest member
Learnexcel1

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