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