Excel pivot table index option

New Contributor

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:

 

https://support.microsoft.com/es-es/office/mostrar-distintos-c%C3%A1lculos-en-campos-de-valor-de-tab... 


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.

customcalculationindex01

7 Replies

@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

Hi Riny, thanks for sharing, but it's not the answer I'm looking for. I need to be sure that the formula is valid in the field of science. Example: to mathematically express a straight line, the equation of the straight line is used. This formula of the straight line is proven to be valid. In contrast, the index formula has no provable mathematical support. There is no information from mathematical blogs about this formula, how do I know that the index formula represents the degree of importance of a value with respect to its row, column and general total? Here is the question.

@osoto10 
If you are not sure in built-in math you may create your own DAX measure with desired logic.

On such model

image.png

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
Thanks Sergei, what I need to know about the Index formula of the pivot table is to show that it is valid mathematically. Is there a theorem or mathematical law that supports this formula?

@osoto10 

 

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.

In your own example,

31 of West-Auto is more sensitive than 31 of West-Prop, because 91 of Auto is less contributing than 93 of Prop.

But, 25 of East-Prop is more sensitive than 38 of Central-Auto because 25 gets compared 47 (54%) and 38 gets compared to 75 (51%) for an almost equal contribution of 93 and 91 to 184 (51% and 49% respectively)
Thus 54%/51%=1.05 is higher than 51%/49%=1.02, signalling more sensitivity

Above, I rounded off for simplicity, more exactly :-
25/47=53.19% ~ 54%
38/75=50.67% ~ 51%
93/184=50.54% ~ 51%
91/184=49.56% ~ 49%
53.19%/50.54% = 1.05
50.67%/49.56% = 1.02