Change Column value based on category value

[Fuhrer786]

Hi Users,

I have 3 products and 5 forecasted models showing 5 diff forecasted values for each product. However in the table chart I only put my top 3 models which I have already decided based on prior data.The sample data looks like below:

Product  Year  Month  Fcstvalue1  FcstValue2  FcstValue3 

A             2019   7        100               200                 125

B              2019  7         2000             5000              3500

c              2019  7          150            200                300

What I know is for product C ,none of my top 3 models are accurate as compared to 4th model. So, I would like to put condition that when product=C then fcstvalue1=fsctvalue4 else fsctvalue1 but I am not able to quite get that DAX query right.

Note: The product field is coming from different table and all foreacasted values are in different  columns in another forecast table like Fsctvalue1,Fsctvalue2,Fsctvalue3,Fsctvalue4 and Fsctvalue5.

Thanks in Advance!

[Cmcmahan]

Sure, this is possible.  What sort of visual are you putting this in? A table? Matrix? Graph of some sort?

If you're using a matrix, you can use this measure:

Fcstvalue1SmartReplace = IF( SELECTEDVALUE(Product[ProductID]) = "C", SELECTEDVALUE(Forecast[fsctvalue4]), SELECTEDVALUE(Forecast[fsctvalue1]))

And replace the FcstValue1 in the values bucket of the visual with this measure.  Note that I'm assuming for any given year/month/product combination, there's only one value for fsctvalue4.  

[Fuhrer786 · Accepted solution]

@Cmcmahan It worked by altering a bit. Thanks a ton!

Model1BestValue = IF(SELECTEDVALUE(Product[ProductID])="C",CALCULATE(sum(Forecast[Fsctvalue4])),CALCULATE(sum(Forecast[Fsctvalue1])))

[Cmcmahan]

Glad you got it working!

Does it make sense to sum multiple forecast values?  For example, if I calculate this in a context where I have all products selected, would I want to SUM all the forecasts together? 

If not, I would replace your SUM aggregations with SELECTEDVALUE expressions, or another aggregation that makes sense.

[Fuhrer786]

Thanks for this solution, but the forecast4 value changes every year-month for product and I am using line and clustered chart