cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ahumke Regular Visitor
Regular Visitor

create a new measure that displays the value of one of two other measures depending upon the X axis

I have a chart displaying a line graph that is showing total credit hours from the years 2014 - 2023.  All of the data is consolidated into one table and was derived from a manual forcast in Excel.  I've paired this with a 'what if' parameter that adjusts the total credits per year if the average per student credit is increased by whatever the user selects on the slider.  

 

All of that is working perfectly but the parameter adjusts the value for the full 10 year span and I'd like it only to adjust for 2019-2023 becasue that is really the only point in time which the credit hours could feasibly change.

 

I have a total credits field that is based upon the average credit hours from the table and then a separate total credits field that is generated by the what if parameter.  My thougth was that if I could create some kind of filter variable that would combine the two into one measure I could use that in the display - but I keep getting errors.  Something along the lines of (but I struggle with my if statements and can't seem to not get an error of multi value or too many arguments)

 

DisplayWhatIfCredits = IF('Dates'[Date].[Year]<2019, CALCULATE(sum('Summarized'[Total Credits])), ('Summarized'[What if average credit]), 'Dates'[Date].[Year]>2018)
1 ACCEPTED SOLUTION

Accepted Solutions
ahumke Regular Visitor
Regular Visitor

Re: create a new measure that displays the value of one of two other measures depending upon the X a

This is the formula I finally got to work:

 

DisplayWhatIfCredits = if (max('Dates'[Date].[Year]) < 2019, CALCULATE(sum('Table'[Total Credits])), Calculate(sum('Table'[Total Credits]))+(sum('MCC Forecasted Enrollment'[Head Count])*'What if average credit'[What if average credit Value]))

View solution in original post

3 REPLIES 3
Microsoft v-danhe-msft
Microsoft

Re: create a new measure that displays the value of one of two other measures depending upon the X a

Hi @ahumke,

Could you please share some sample data to have a test and post your desired result if possible?

 

Regards,

Daniel He

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

Re: create a new measure that displays the value of one of two other measures depending upon the X a

Here is the sample data.   

Sample Data

From this point, the average credits need to have a measure created.

 

Average Credits = sum('Table'[Total Credits]) / sum('Table'[Head Count])
 
Then a what if parameter is set for that - I did a range from -8 to 8 with a 1 point increment.  
 
Then I calculated a "What if average credit" field that takes the user input from the parameter and recalculates what that impact would be on total credits.
 
What if average credit = (('Average Credit Hours'[Average Credit Hours Value]* (sum('Table'[Head Count])) + (sum('Table'[Total Credits]))))
 
The bar chart shows total credits with fiscal year as the X axis - then I have the "What if average credit" total credit value as a line over the bar chart.  I'd like the line to reflect the total credit value for 2014-2018 and reflect the "what if average credit" value from the user input parameter for years 2019-2023.  
 
So for example, if the user enters an increase of 2 credit hours per student in the slider - the line in the chart would show a total credit value of 95,096 for 2018 (total credit value) and then 2019 would show 114,997 (the what if value).  
 
thanks!
ahumke Regular Visitor
Regular Visitor

Re: create a new measure that displays the value of one of two other measures depending upon the X a

This is the formula I finally got to work:

 

DisplayWhatIfCredits = if (max('Dates'[Date].[Year]) < 2019, CALCULATE(sum('Table'[Total Credits])), Calculate(sum('Table'[Total Credits]))+(sum('MCC Forecasted Enrollment'[Head Count])*'What if average credit'[What if average credit Value]))

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)