Forum Discussion
Excel pivot table index option
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.
- 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.
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
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
