Excel - getpivotdata function 1

[arvarr]

Hi
Is there a way to sum multiple items from a powerpivot when it is referencing to 2 cells?

A1 = Pivot data field
B1 = Pivot data
C1 = Field 1
D1 = Item 1
E1 = Field 2
F1 = Item 2
G1 = Item 1a
H1 = Item 1b

Formula = getpivotdata(A1&"",M1,C1,D1,E1,F1) - If referencing to only Item 1, it works
(M1 = Row Label where the powerpivot is located)

Formula = getpivotdata(A1&"",M1,C1,{"[{xxx],[yyy],[zzz]"},E1,F1) - If referencing to Item with actual cell text, it works

However, if referencing to cell range, it doesn't work
I.e. Formula = getpivotdata(A1&"",M1,C1,{D1,G1,H1},E1,F1) - If referencing to only Items with actual cell text, it works

Thanks,
arv

 

[SkipVought]

Arv,

Pivot Tables are a great "quick n dirty" aggregation tool.

But for getting aggregations in general I've been using the SUMPRODUCT() function, which does what GETPIVOTDATA() does and lots, LOTS more like also replacing SUMIFS() and COUNTIFS() and, at least to me, SUMPRODUCT() is much MUCH more intuitive.

This assumes that you have the source data table that your PT is aggregating. In my world, ALL my source tables are Structured Tables. It makes your data analysis and reporting applications more nearly self documenting.

With reference to this specific issue...
What happens in vagueness stays in vagueness!

Please provide specific data and the specific results you expect.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

So I don't know what your data looks like.

Here's mine...

Source Data...
[pre]

Country Salesperson Order Date OrderID Order Amount
USA Fuller 1/1/09 10392 1440
UK Gloucester 1/2/09 10397 716.72
UK Bromley 1/2/09 10771 344
USA Finchley 1/3/09 10393 2556.95
USA Finchley 1/3/09 10394 442
UK Gillingham 1/3/09 10395 2122.92
USA Finchley 1/6/09 10396 1903.8
USA Callahan 1/8/09 10399 1765.6
USA Fuller 1/8/09 10404 1591.25
USA Fuller 1/9/09 10398 2505.6
USA Coghill 1/9/09 10403 855.01
USA Finchley 1/10/09 10401 3868.6
USA Callahan 1/10/09 10402 2713.5
UK Rayleigh 1/13/09 10406 1830.78
USA Callahan 1/14/09 10408 1622.4
USA Farnham 1/14/09 10409 319.2
USA Farnham 1/15/09 10410 802
...
[/pre]

Pivot Table

[pre]
Sum of Order Amount Column Labels
Row Labels UK USA Grand Total
2009[highlight #FCE94F]
Jan $15,176 $49,915 $65,090[/highlight]
Feb $2,896 $28,546 $31,442
Mar $7,167 $40,347 $47,514
Apr $17,992 $13,605 $31,596
May $13,171 $50,804 $63,975
Jun $11,860 $38,223 $50,083
Jul $11,452 $26,093 $37,545
Aug $13,009 $43,259 $56,268
Sep $19,546 $30,962 $50,507
Oct $23,850 $50,455 $74,305
Nov $13,886 $26,557 $40,443
Dec $6,838 $53,582 $60,420
Grand Total $156,844 $452,347 $609,191
[/pre]

Contrasting formulas[highlight #FCE94F][tt]
=GETPIVOTDATA(
"Order Amount",$A$4,
"Order Date",ROW(A1),
"Years",2009)

=SUMPRODUCT(
(TEXT(source_data[Order Date],"mmm")=A7)*
(YEAR(source_data[Order Date])=--$A$6)*
(source_data[Order Amount]))[/tt][/highlight]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
Thanks for the information.
I dont have access to the source data. The pivot table is a power pivot supplied.
I have attached an example of what i am trying to achieve.

1. I need a formula in column D to handle the multiple items
2. Column N to P are just formula where i am trying to determine Item start cell and Item finish cell to see if i can use this into the formula (given that some rows will have 1 item, some with 2 items, some with 3 items, some with 4 items, etc..)
3. Note that Incidentals at this stage is only associated with Type 4. At some point in time, it may be used. The formula ideally can tackle this requirement as well.

Thanks,
arv

 

[SkipVought]

I dont have access to the source data.

Your source data is in A2:C18. However, your PT does NOTHING. It aggregates NOTHING. It is useless!

What is it that you want to do? Forget Pivot Tables and formulas! What question do you want to answer?

Actually, your Data Table looks like an aggregation itself.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

Actually this might be a more enlightening PT, maybe, if we knew what question you were answering.

[pre]
Sum of Amount Column Labels
Row Labels Type 1 Type 2 Type 3 Type 4 Grand Total
Air Fares 2000 1000 100 3100
Fees
Hotel 1500 500 2000
Incidentals 100 100
Parking 50 20 100 170
Taxis 100 200 300
Uber
Grand Total 3650 1720 200 100 5670
[/pre]



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

Also, your Table in YELLOW HIGHLIGHT makes no sense when compared to your actual data in A2:C18.

Type 4 has only data for Incidentals. But you show Air Fares, Hotel & Taxis
AND Incidentals are listed in Type 2 with parking and air fares???!!!

I just don't get what you're attempting to do.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Your source data is in A2:C18
- These are not real data. With the real working file, it is some sort of power pivot that is only supplied and I do not have access to any raw data.

Type 4 has only data for Incidentals. But you show Air Fares, Hotel & Taxis
- My bad. This should read as Type 1

Your source data is in A2:C18
- I intentionally included Incidentals in Type 2 to see if the formula can cater to this situation

I added another scenario in Row 31 (i.e. a gap between J31 and L31).

What i am trying to understand is what formula can be applied to those blue cells so that I can apply them to the real data.

Thanks,
arv

 

[arvarr]

File attached with the additional scenario

 

[SkipVought]

I still don't understand what you're trying to do with this data.

Without reference to pivot tables or formulas, explain what you want to accomplish. So far I have not the foggiest idea.

Maybe it's because there's been a disconnect between your data and your test set in rows 25 and following. Things don't match up, or am I missing something?
Why 2 Type 2 rows?

Did you see my PT? This shows what Categories go with what Classes.
Type 1 & Type 2 contain 4 Categories
Type 3 contains 2 Categories
Type 4 contains 1 Category

Help me out.

Do you need to upload a new corrected workbook?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
There are 2 type 2 rows because:
1. Hotel only
2. Incidentals, Parking and Air Fares

Row I2531 are just my mappings.
The blue cells are formula that i need.

Yes, I did see your pivot table.

I will still need the formula to be driven by the mappings in I2531.

Thanks,
arv

 

[SkipVought]

The formula works ONLY when there are VALID values in your Item array.
[tt]
D26: =IFERROR(SUM(GETPIVOTDATA($J$2&"",$H$2,$G26,$I26,$H26,OFFSET(J26,0,0,1,COUNTA(J26:M26)))),"")
[/tt]

The OFFSET() function returns an array to the last argument for one or more Categories of the GETPIVOTDATA() function.

If you have an INVALID value in the Categories array for a given Class, an ERROR results. You can use Data>Validation to keep the values "clean."

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
Thanks heaps. The offset formula is exactly what i need.

Thanks again for your help.

Regards,
arv

 

[SkipVought]

The Data > Validation > List can be accomplished with these steps...

1) Define the Structured Table range in the Formulas > Name Manager as...

a) rCategory
=tTYPE[Category]
b) rClass
=tTYPE[Class]

2) Data > Validation > List formula-- selection J26:M30
=OFFSET(rCategory,MATCH($I26,rClass,0)-1,0,COUNTIF(rClass,$I26),1)


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
Great stuff.. Thanks for the information for the data validation list.
Works like a charm.

Regards,
arv

 

[SkipVought]

Arv,

I've enjoyed working on this challenge of yours.

I like simple solutions.

IMNSHO, the Pivot Table in this particular instance is a totally unnecessary useless appendage. It complicates and clutters your appication.

Back to the SUMPRODUCT() multi-functional function.

I'm looking for simplicity and clarity.

Compare
[pre]
=IFERROR(SUM(GETPIVOTDATA($J$2&"",$H$2,$G26,$I26,$H26,OFFSET(J26,0,0,1,COUNTA(J26:M26)))),"")
=SUMPRODUCT((tTYPE[Class]=I26)*(tTYPE[Category]=OFFSET(J26,0,0,1,COUNTA(J26:M26)))*(tTYPE[Amount]))
[/pre]

As I stated earlier, I like the simplicity of using equalities: =, <>, >, < resulting in TRUE/FALSE results for each (criteria expression) that effects the aggregations in the array.

In working at this problem, I learned a thing or two: one of them being the use of the OFFSET() array function in the SUMPRODUCT() function.

One caveat for your Source Data Table. The Class column must be the last sort, in Ascending order.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[arvarr]

Hi Skip
Sorry for the late response.
Likewise, I would very much prefer the sumproduct formula as its much clearer and simple.
I.e. if i had the source data handy, that would be my go to option.
However, the complexity is the data source is not avail to me.
I only have access to the power pivot output.

Definitely learnt more than one thing from this post .

I'll be posting another question shortly. Looking forward to your response.

Thanks again Skip.

Cheers,
arv