cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DuaneDLB Frequent Visitor
Frequent Visitor

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

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: 

Matrix.JPG

Screen Shot 2:

Forecast Values.JPG

1 REPLY 1
Highlighted
Super User I
Super User I

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

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! 🙂


Did I answer your question? Mark my post as a solution!
Kudo's are appreciated and considered a motivation.

Proud to be a Datanaut!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors