Extracting Multiple data points from single Pivot Table

[Scuba]

Hello,

I have a pivot table set up (see below), with slicers for the Year & Month & mode, when the user selects a given Year, Month & Mode, I can extract the data no problem for a single Month, however, if the user selects more than one month at the same time, I am stuck.

For example if the user selects Year 2022, Month Feb & Mode Air, I can can easily extract 886294.3 as the correct value, however, if the user selects Feb & Apr, I do not know who to extract multiple points of data and then combine the values together for reporting.

Is anyone able to help, enlighten me on what I could do here?

Pivot table below:



Sum of Noatum Inv. CHATIL GBP

Mode






Year
Month Name
Air
Ocean
Sea-Air
Truck
Grand Total


2022
Jan
1251994.16
249490.16

54451.53
1555935.85



Feb
886294.3
346610.44

95834.21
1328738.95



Mar
1335004.59
212888.38

104424.86
1652317.83



Apr
1202239.53
241842.563

113192.62
1557274.713



May
1185394.11
241986.497
14302.57
119249.93
1560933.107



Jun
2095730.65
92167.3

93832.86
2281730.81



Jul
2460308.62
115627.68

128940.95
2704877.25



Aug
1756212.9
361831.4

145028.28
2263072.58



Sep
2019019.31
313097.22

106768.87
2438885.4



Oct
849161.31
226330.04

168454.7
1243946.05



Nov
1181327.36
790491.3967

170129.83
2141948.587



Dec
732611.01
961748.1333

131032.98
1825392.123


2022 Total

16955297.85
4154111.21
14302.57
1431341.62
22555053.25


2023
Jan
248257.31
1205177.603

158167.53
1611602.443



Feb
771581.28
737627.733

288040.88
1797249.893



Mar
549326.48
972323.1638

307490.33
1829139.974



Apr
172893.52
1015375.184

197668.71
1385937.414



May
271808.58
705721.9176

141897.77
1119428.268



Jun
260812.31
638372.8668

162893.47
1062078.647



Jul
321144.62
568981.7019

160842.44
1050968.762



Aug
305352.89
264738.5799

177473.87
747565.3399



Sep
55753.59
337369.5334

207111.57
600234.6934



Oct
178495.6
320089.1467

157216.8
655801.5467



Nov
95417.76
214326.03

112625.95
422369.74


2023 Total

3230843.94
6980103.46

2071429.32
12282376.72


Grand Total

20186141.79
11134214.67
14302.57
3502770.94
34837429.97



Thank you.

[Paul_Hossler]

Can you attach a workbook with the raw / list and the PT?

Also maybe a manual calculation for Feb and April?

Mean whiles, you can try the attachment

Another approach might be to use the GETPIVOTDATA() function

[Scuba]

Hi Paul,

Thank you for you quick response, I can't share the original file as it has sensitive data in it, however, I have recreated part of what I'm talking about in the attached file.

You can ignore what the left hand PIVOT table is doing when you use the slicers, on the original workbook, this is driving various metrics/ chart/KPI's etc, on my recreation, I have not added the charts/KPI etc.

I have shown the manual calculations for Feb & Apr, I have also shown the formula, which works perfectly for 1 line but I need to be able to more dynamic and what aver month selection the user selects with the slicer, I need to be able to return the relevant values and totals.

Hopefully I have been clear enough, if not please do not hesitate to ask more questions :)

Once again, thank you for your help Paul.

[Paul_Hossler]

OK, that helps

I can see the slicers (J-L) control PT's, but it seems complicated.

Why not just not use them on the main PT (B-F) and only use the main PT?

I don't understand the need for the other 4 PTs, N, O, P and S-X

That seems to get you just what your 'Manual Values] (Green) shows, including Slicer selections, format, etc.

Are you looking for formulas, or a VBA solution?

I'm thinking that a User Defined Function might be required if the PT in B2 isn't enough

[Scuba]

Hi Paul,

Yes, I can see it looks overly complicated, that's because I have replicated part of what I am trying to do rather than share the original files as I cannot due to sensitive data.

Basically, the original sheet is driven by slicers as indicated that are attached to a different set of Pivot tables/database, from these slicers I am obtaining the given 'Year', 'Mode' & 'Month' that the user selects.

Once I have this information, I would then like to return a given set of data against another pivot table i.e. be able to extract singular line data for 'Year', 'Mode' & 'Month' that the slicers has given us, this I can do, however, if the user selects multiple months ,for example Feb & Apr, my formulas then fail.

I need a formula read the slicer information when multiple months are or modes are selected and then write formula that pulls all the information in, this can be in excel or VBA, excel might be easier for what I am doing but if the answer is in VBA so be it.

Hopefully, that’s a bit clearer now Paul, let me know if not.

And once again, thank you for you time and patience.

Regards

Richard

[Paul_Hossler]

31259

But which number or numbers do you want?

a, the total for each Mode for all selected months (2)

b. The total for each month and mode (4)

c. Grand total (1)


If you want the 2 dimensional format in your "Manual Values" that seems like the PT is the way to go

[Scuba]

Hi Paul,

That all depends on what the user selects, if they only select Air then I only need to pull the air values in for the given month & year but if they select all 3 modes (Air, Ocean & truck) then i need to pull all 3 modes, I would need to replicate the line level details so if Air is selected then show month name, followed by total for that month, if 2 different months are shown then both months shown individually with a total at the bottom.

Hope thats clear?

regards

Richard

[Paul_Hossler]

Sorry to be dense, but a PT will do all that.

312603126131262

You said 'extract' in post #1

Do you want to extract the selection values (Feb, Apr) or the money values to use in a (e.g.) management report?