Excel pivot table index option

[osoto10]

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.

[#2]

@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

[#3]

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.

[#4]

@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

[#5]

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?

[#6]

@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.

[#7]

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

[#8]

@osoto10 

If about math perhaps these links will be useful

1.3 Paasche's Price Index - YouTube

1.4 Laspeyre's Price Index - YouTube

1.5 Index Numbers - YouTube

Pivot Table Index Function | MrExcel Message Board

[#9]

Refer to the concept of expected value and joint probability. Its the neutral independent value. You divide the Observed with the Expected value. In simple terms there are total 6 values in your table excluding totals. So, if you distribute 6 across 6 cells equally then each cell is 1, as the constrain is that the grand total will be 6. Relative importance is got by solving the question "How can i distribute 6 across the 2x3 table such that the row total comes to 2 and column total comes to 3 and the value should reflect the proportion of actual value. You will arrive at the formula (Row Total x Column Total)/Grand Total . Divide the actual value in the cell with the ideal proportional value , you get the index. For example 1.02+0.95+1.01 will give you 3 and 0.98+1.05+0.99 will give you another 3. 3+3=6. Thus index has solved the question "How can i distribute 6 across the 2x3 table such that the row total comes to 2 and column total comes to 3 and the value should reflect the proportion of actual value.