Mar 31 2022 06:42 PM - edited Mar 31 2022 06:44 PM
Good evening, I have a question: Pivot tables have an option called "Show values as" and in turn have several categories. One of these categories is "Index". On the microsoft page they specify the index formula:
And in internet blogs they explain that this function measures the degree of import of a value in the context of the row and column where it is located and also the value of the general total. What I want to know is the mathematical underpinning of the index formula? I mean, is there any theory about it? otherwise I will consider it an unreliable formula.
Mar 31 2022 09:15 PM
@osoto10 Perhaps the article in the link below clarifies it.
https://www.myexcelonline.com/blog/index-in-pivot-table/
In your example the index for Auto / Central = 38 x 184 divided by 91 x 75
Apr 01 2022 10:32 AM
Apr 02 2022 04:02 AM
@osoto10
If you are not sure in built-in math you may create your own DAX measure with desired logic.
On such model
to repeat existing Index
justTotal:=SUM(Table1[V])
Index:=
VAR grandTotal =
CALCULATE ( [justTotal], ALL ( Table1 ) )
VAR rowTotal =
CALCULATE ( [justTotal], ALL ( Table1[B] ) )
VAR columnTotal =
CALCULATE ( [justTotal], ALL ( Table1[A] ) )
RETURN
[justTotal] * grandTotal / rowTotal / columnTotal
Apr 02 2022 04:51 PM
Apr 02 2022 10:55 PM
It can be understood in simple unitary terms. Referring to the example in the article link shared by @Riny_van_Eekelen , I've tried to distil the interpretation,
Index = Numerator / Denominator
Numerator = Value of cell / Grand Row Total
Denominator = Grand Column Total / Grand total of Grand Totals
or,
Numerator = Bottles sales in America / Bottles Sales across Regions
Denominator = America's sales / Global sales
or,
Numerator = Normalisation of Bottles sales across regions
Denominator = Normalisation of region sale across Globe
(Normalisation just changes figures to the scale of 1 in both Num. and Denom.)
or,
Numerator = "How much contributing" is the "American" "bottle" sales in "overall bottle" sales
Denominator = "How much contributing" is the "American" sales in "overall global" sales
So the fraction,
Index[american bottle] = Numerator / Denominator, at global scale,
1) Increases if "American" "bottle" sales increase
2) Decreases if "American" "bottle" sales decrease
3) Decreases if "American" "overall" sales increases as compared to other regions
4) Increases if "American" "overall" sales decreases as compared to other regions
1) and 2) are direct to understand that as the "American" "bottle" sales change (increase or decrease), so does the Index, so index in a way tells about the "influence" of american bottle in overall sales.
3) and 4) take into account how contributing is the region ("America") itself the to global sales. If America itself has a smaller %age contribution to global sales, then american bottle index (="influence") is bigger because same change in the absolute value of bottle sales in america would lead to bigger change in its %age contribution to overall sales as compared same change in the absolute value of bottle sales in some other region like Europe which was already contributing hugely to global sales. Thus the american bottle price change is more "sensitive" than European bottle price change.
Apr 02 2022 11:23 PM
Apr 03 2022 07:52 AM
If about math perhaps these links will be useful
1.3 Paasche's Price Index - YouTube
1.4 Laspeyre's Price Index - YouTube
Pivot Table Index Function | MrExcel Message Board
Dec 08 2023 09:45 PM