cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## 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 (ROE) metric  (# of enrollments divided by # of applications for each country of origin).  Example, if we have 1 student enrolled from Bolivia but we had 2 applications, then the ROE for Bolivia is 50%.   Also, the ROE can change from one term to another but I have alwas found that using the ROE from the previous term keeps projections accurate.

So, how can build a calculation/meaure that projections the expected number of enrollments for an upcoming term based on the ROE of each country (from the previous term) and the number of applications we have for the upcoming term (from that country)?

Many thanks in advance for suggestions.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Unique Projection Calculation

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Community Support Team

## Re: Unique Projection Calculation

Hi @Anonymous,

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

## Re: Unique Projection Calculation

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

Anonymous
Not applicable

## Re: Unique Projection Calculation

I am not sure why the link didn't work.  I data is in a CSV that I uploaded to One Drive.  https://1drv.ms/u/s!Am5Kn6DgQya9hUog6Ctd4kv9KO8g

Community Support Team

## Re: Unique Projection Calculation

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

## Re: Unique Projection Calculation

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!!
Community Support Team

## Re: Unique Projection Calculation

Hi @Anonymous,

To create another new measure.

`Measure 2 = SUMX('Data for ROE Projections',[Projected New])`

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: Unique Projection Calculation

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 6 members 2,208 guests
Recent signins: