Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Unique Projection Calculation

Hello   I am trying to build a calculation that will project our expected enrollment based on our ROE metric.  My company deals with interntional student enrollments and we use a Rate of Enrollment...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

     

    Regards,
    Frank

Anonymous's avatar
Anonymous
Not applicable
7 years ago

 

 

Hello, thank you for responding.  Here is sample data file I am working with.  https://1drv.ms/u/s!Am5Kn6DgQya9hUog6Ctd4kv9KO8g 

 

Note: I want to use the ROE for each country in fall 2018 (since this was the last term) and apply the ROE percentages to the 2019 application#.   For example, China had a 31.7% ROE in fall 2018 (highlighted on file), how can I apply the 31.7%ROE to the 29 applications China has for spring 2019 (also highlighted)?

 

 

    • v-frfei-msft's avatar
      v-frfei-msft
      Community Support
      7 years ago

      Hi Anonymous,

       

      To create a measure as below.

       

      Measure = 
VAR roelast =
    CALCULATE (
        SUM ( 'Data for ROE Projections'[ROE] ),
        FILTER (
            ALL ( 'Data for ROE Projections' ),
            'Data for ROE Projections'[Year]
                = CALCULATE (
                    MAX ( 'Data for ROE Projections'[Year] ),
                    ALL ( 'Data for ROE Projections' )
                )
                    - 1
                && 'Data for ROE Projections'[Semester] = "Fall"
        ),
        VALUES ( 'Data for ROE Projections'[Country] )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Data for ROE Projections'[Year] )
            = CALCULATE (
                MAX ( 'Data for ROE Projections'[Year] ),
                ALL ( 'Data for ROE Projections' )
            )
            && SELECTEDVALUE ( 'Data for ROE Projections'[Semester] ) = "Spring",
        roelast,
        BLANK ()
    )

      Also please find the pbix as attached.

       

      Regards,

      Frank

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago
        THANK YOU!!! Frank, this was super helpful. I was able to create an additional measure called Projected New which is correctly projecting the # of new student by country for the sprig 2019 term. However, the grand total (of the Projected New measure) is way off. Any ideas? Here is the revised document: https://1drv.ms/u/s!Am5Kn6DgQya9hUwN_a2o34SrNWm5 Again, many thanks for your help!!