Forum Discussion

ecoale's avatar
ecoale
Helper I
8 years ago
Solved

Students variations year over year

Good morning, I need help.......   I have imported (by Excel) a dataset "Students", with 3 coloumns: Student ID, Sex (M/F) and Year (from 2009 to 2016). Thanks to the Power BI Matrix item...
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    ecoale,

     

    You may refer to the following measure.

    Measure =
VAR y =
    SELECTEDVALUE ( Students[Year] )
VAR prev =
    CALCULATE ( COUNT ( Students[Student ID] ), Students[Year] = y - 1 )
RETURN
    IF ( NOT ( ISBLANK ( prev ) ), COUNT ( Students[Student ID] ) - prev )
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    ecoale,

     

    Just change it as follows.

    DIVIDE ( COUNT ( Students[Student ID] ) - prev, prev )
v-chuncz-msft's avatar
v-chuncz-msft
Community Support
8 years ago

ecoale,

 

Glad to hear that. You may help accept solution. Your contribution is highly appreciated.

ecoale's avatar
ecoale
Helper I
8 years ago

Good mornig my friend! I would need unother help of yours... :/

 

I have now a table (a visual object in PowerBI desktop) with the students variations year over year (I got them as % difference over the previous year). In other words, I had this (in other visual object, using Matrix item):

Year             2009      2010     2011     2012     2013     2014     2015     2016

Students        835        767       728       642       557       538       522       524

-----> and later I got this (thanks tou your helps):

Year             2009      2010     2011     2012     2013     2014     2015     2016

Δ%YoY                      -8,14    -5,08    -11,81   -13,24    -3,41    -2,97    (+)0,38

 

Now, using the item Line chart and histogram with grouped columns, I got this (refering the variations YoY):

 

And here the question: how can I get a line crossing the single seven bars over, expressing the annual average variation (that, in this case, is +6,5%), by the calculation of the GEOMETRIC MEAN?

Ps: I saw the functions "geomean" and "geomeanx", but I do not know how to use them; also because it seems to me they are useful if calculated using the columns of the basic dataset uploaded, and not with numbers (my variations YoY) - in a visual object (matrix) - already deriving from PowerBI functions...

May you help me??? :(