Handling Nulls in Date slicer aggregations

[simam]

Hi team,

 I have created a measure which calculates the deviation of total value from a target value. The total value can be null sometimes and in this case I would like to see null for the deviation as well. 

here is the measure to calculate the deviation 

IF (SUM('Sawmill Performance Data [Total]) <>BLANK() && sum('Sawmill Performance Data '[Target])<>BLANK() ,divide((sum('Sawmill Performance Data '[Total])-SUM('Sawmill Performance Data '[Target])),SUM('Sawmill Performance Data'[Target])),BLANK())

I also have a date slicer in that page. when there is only one date selected, the above formula works correctly, but when for example 2 days is selected where one of the days has null for total but not null for target, then it adds the target of both days and total of both days and calculates the deviation. In this case, the deviation would be off as it shouldn t add the target of the day which the total was null.

Business wants to see the nulls as it shows there was no production.  so I cannot just exclude the data where total is null.

I really appreciate if you could help me out. 

[Nathaniel_C]

Hi @simam ,

Nicely laid out problem!

Thinking this through, but close to quitting time, will be thinking about it overnight. Perhaps this post by virtue of going to the top of the pile will be answered by someone else, too. 

So, one thought is to do a count of dates, and if more than one, do another test.  This might even be a switch. In which case, test for any null, and don't use the table value for the other, but use null. Thus if Total is null then in the calculation make Target null for that date. (Not changing the table value of course.) 

Or instead of a count of dates, wrap this with an if statement to test for Values that make up the Total, and Target, and do the same change to null.

Will try to do something with tomorrow.

Hope this  helps,

Nathaniel

[Nathaniel_C]

Hey @simam , have you come up with anything? You could use BLANK() for AllSelected [Total] and [Target], but I don't see how to iterate through the rows to find that. Could you create a table (based on selected dates) then check the table for values, then replace the values with a null (if the other value is null) and then perform your measue on that table?

Or is the solution to step back and go at it from another way?

Nathaniel