Using "WhatIf" Parameters in a Dax Measure. Need Help with Totals

DuaneDLB — Mon, 20 Jan 2020 16:12:16 GMT

I created 3 WhatIf Parameters and joined them to a table in my dataset to project forecasted information. When I joined the 3 WhatIf Parameters to the table, I needed to make sure I apply the Whatif Value to the correct record in the table. Below are two screen shots to help illustrate my problem.

Screen Shot 1 is marked with the three values I am trying to re-compute the wages using the WhatIf parameters. The information is correct for all 3 parameters but you will notice the total for the Rate Forecast column is the same as the Wages.

Why does the Rate Forecast total equal the Wages when you can clearly see changes were made to each line item in the Rate Forecast Column. Screen Shot 2 illusrates the Dax Measure I used to populate the Rate Forecast values. Any Advice? Thanks

Screen Shot 1:

Screen Shot 2:

Re: Using "WhatIf" Parameters in a Dax Measure. Need Help with Totals

JarroVGIT — Mon, 20 Jan 2020 19:35:13 GMT

Hi DuaneDLB ,

Measures are evaluated in every context it is visualized. To be more specific; if you have a measure like this:

Measure = SUM(Table[SalesAmount])

and you create a card of it, it will sum the SalesAmount column that is currently in the context. The context is modified by slicers, filters or by visuals. If you have a table (or matrix), every occurence of the measure is a seperate evaluation of said measure in that context. In the example of above, if I would create a table with in the first column all months, en then in the second column the above measure, the measure would be evaluated against a context that is modified by the first column. In other words, the dataset this measure is calculated on is filtered on the specific month. So far, so good.

In your measure, you use SELECTEDVALUE(). This will be true if the whole column you are referencing is having only one distinct value. If it has multiple values, the statement IF(SELECTEDVALUE(<column>) = "xxx"... will always be false because there simply isn't a SELECTEDVALUE, there are multiple. I mention this because you might think that what you are currently seeing is the SUM of every subline under "Pay Description" rows, but it really is not. It is the measure evaluated in a context where the data is filtered on "Pay Description".

Anyway, a common misconception is that the Total row in tables and matrixes are simple sums of the columns in the visual. They are NOT in the case of Measures. The measure is simply re-evaluated on a dataset unfiltered by the columns in your visual. Since your measure checks multiple times for SELECTEDVALUE()="xxx", which always results in FALSE in a unfiltered dataset with multiple values in pyt_itemcode, only the last part is evaluated (SUM(pyd_ammount)).

I hope this helps. If you do want your measure to evaluate differently for different levels in your visual, you might need to create a bit more complex measure and use ISINSCOPE(). I found this to be a fantastic tutorial on that. Let me know if you have any questions 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

topic Using "WhatIf" Parameters in a Dax Measure. Need Help with Totals in DAX Commands and Tips

Using "WhatIf" Parameters in a Dax Measure. Need Help with Totals

Re: Using "WhatIf" Parameters in a Dax Measure. Need Help with Totals