Forum Discussion

osoto10's avatar
osoto10
Copper Contributor
Apr 01, 2022

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 ...
excel
SergeiBaklan's avatar
SergeiBaklan
MVP
Apr 02, 2022

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
osoto10's avatar
osoto10
Copper Contributor
Apr 02, 2022
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?
  • amit_bhola's avatar
    amit_bhola
    Iron Contributor
    Apr 03, 2022

    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.

    • amit_bhola's avatar
      amit_bhola
      Iron Contributor
      Apr 03, 2022
      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

Resources