Forum Discussion

ecoale's avatar
ecoale
Helper I
8 years ago
Solved

GEOMEAN???? :(

Hi my friends!

I would need help with geometric mean...

 

I have a matrix with the number of students per year (2009/2017).

In a plot I got the % variations YoY (Δ%),as following:

 I used the following calculation:

 

Δ =
VAR y =
SELECTEDVALUE ( ISCRITTI[AA] )
VAR prev =
CALCULATE ( COUNT ( ISCRITTI[Student_ID] ); ISCRITTI[AA] = y - 1 )
RETURN
DIVIDE ( COUNT ( ISCRITTI[Student_ID] ); prev )

 

and then:

 

Δ% = (ISCRITTI[Δ] * 100) - 100

 

Now, I would like to get a mesure wich provided the geometric mean of the variations (not %) YoY, that is the Δ values (not Δ%).

May you help me????

Thanks a lot

Ale

Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    Hi again ecoale

    Thanks for the additional explanation. It sounds like you want to see the same constant geometric mean across all years.


    Try either of these measures:

     

    Geometric Mean of Δ =
GEOMEANX ( ALLSELECTED ( ISCRITTI[AA] ), [Δ] )

     

    or

     

    Geometric Mean of Δ =
CALCULATE ( GEOMEANX ( VALUES ( ISCRITTI[AA] ), [Δ] ), ALLSELECTED () )

    My original measure would have worked as a standalone measure on a card, but not when filtered on an individual year.

     

    Regards,

    Owen

  • OwenAuger's avatar
    OwenAuger
    8 years ago

    Glad to hear it worked :)

     

    For your N* measure you should be able to do something like this (it worked for me in a test model):

    N* =
VAR y0 =
    FIRSTNONBLANK ( ALLSELECTED ( ISCRITTI[AA] ), 0 )
VAR y =
    SELECTEDVALUE ( ISCRITTI[AA] )
VAR N0 =
    CALCULATE ( COUNT ( ISCRITTI[Student_ID] ), y0 )
VAR Mg = [Mg]
RETURN
    N0 * POWER ( Mg, y - y0 )

    I have used variables to make it quite clear what the components of the calculation are.

     

    Essentially we just need to multiply the initial Student Count by Mg raised to the power of the difference between last and first years.

     

    Regards,

    Owen

7 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    8 years ago

    ecoale

     

    You can use the GEOMEANX function to calculate the geometric mean of a measure over a particular dimension.

    If I have interpreted your post correctly, ISCRITTI[AA] is the dimension you want to iterate over, and [Δ] is the measure.

    So a measure like this should work:

     

    Geometric Mean of Δ =
    GEOMEANX ( VALUES ( ISCRITTI[AA] ), [Δ] )

    Does this give the expected result?

     

    Regards,

    Owen

     

     

    • ecoale's avatar
      ecoale
      Helper I
      8 years ago

      Dear Owen,

      I thank you for your reply, but unfortunately it is not what I am looking for.

      I got a mesure changing year after year... (as in the image enclosed). 

      I try to indicate here the calculation I would need:

      Δ_10/09 = 46.859/47.092 = 0,995

      Δ_11/10 = 46.925/46.859 = 1,001

      ...

      Δ_17/16 = 45.910/47.175 = 0,973

      All these N values - I mean all the quotiens Δ_(.../...) above - are calculated by my measure Δ (that I wrote here in the previous post): then, I would like to get the geometric mean of these N quotients, that we know it is: (0,995*1,001*...*0,973)^(1/N), getting in this way a single (geometric) mean - of the variations YoY - for the entire period 2009/2017.

       

      Better?

       

      • OwenAuger's avatar
        OwenAuger
        Super User
        8 years ago

        Hi again ecoale

        Thanks for the additional explanation. It sounds like you want to see the same constant geometric mean across all years.


        Try either of these measures:

         

        Geometric Mean of Δ =
GEOMEANX ( ALLSELECTED ( ISCRITTI[AA] ), [Δ] )

         

        or

         

        Geometric Mean of Δ =
CALCULATE ( GEOMEANX ( VALUES ( ISCRITTI[AA] ), [Δ] ), ALLSELECTED () )

        My original measure would have worked as a standalone measure on a card, but not when filtered on an individual year.

         

        Regards,

        Owen