Forum Discussion

ecoale's avatar
ecoale
Icon for Helper I rankHelper 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 and the function ID Count = COUNT(Students[Student ID])", I got a summary table in which I have the count of students (recognized thanks the Student ID) by the variables Sex and Year.

Now, I would like to get a new row in the table, with the variation of students between two years: foe example, the quotient betwwen the count of males students in 2010 and the count of males students in 209 (for each years and sexes).


I also saw there is the fast mesure for this, but I am not able to use it...

Please may you help me?

  • 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 )

7 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    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 )
    • ecoale's avatar
      ecoale
      Icon for Helper I rankHelper I
      8 years ago

      My friend thanks a lot, it works!

      But is is not correct: it gives me the difference beetween the two numbers, but I would need the quotient.

       

      Infact, at the moment, for example for the females (F), I have these counts of students (por year):

      2009   2010   2011   2012   2013   2014   2015   2016

        232     223     186     166     153     177     192     218

                     -9      -37      -20     -13       24       15       26

       

      I would need the measure gave me, for example not 223 - 232 = -9.

      But ( (223 / 232) * 100) ) - 100 = 96,1 - 100 = -3,9

       

      May you help me again??

      Thank you!

       

      • v-chuncz-msft's avatar
        v-chuncz-msft
        Icon for Community Support rankCommunity Support
        8 years ago

        ecoale,

         

        Just change it as follows.

        DIVIDE ( COUNT ( Students[Student ID] ) - prev, prev )
  • ecoale's avatar
    ecoale
    Icon for Helper I rankHelper I
    8 years ago

    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 and the function ID Count = COUNT(Students[Student ID])", I got a summary table in which I have the count of students (recognized thanks the Student ID) by the variables Sex and Year.

    Now, I would like to get a new row in the table, with the variation of students between two years: foe example, the quotient betwwen the count of males students in 2010 and the count of males students in 209 (for each years and sexes).


    I also saw there is the fast mesure for this, but I am not able to use it...

    Please may you help me?