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

[ahumke]

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)

[v-danhe-msft]

Hi @ahumke,

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

Regards,

Daniel He

[ahumke]

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 · Accepted solution]

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