HELP with DAX (Conditional Measure)!!!!

[Debsidrian]

I want to create a column with a calculated value:

If the Value in "sum of fines" is < average fines, return (Average fines - sum fines) if not return "sum of fines"

I am using this DAX: 

ConditionalValue =

IF(

    SELECTEDVALUE(Record[fines])

    <[TotalAvg],

    [TotalAvg]-([fines average per dc_nbr]),

  SELECTEDVALUE(Record[fines])) 

TotalAvg = CALCULATE([fines average per dc_nbr],ALL('Record'[dc_nbr]))

but is not returning the value when is Fines>average

[Anonymous · Accepted solution]

Hi @Debsidrian ,

I’d like to acknowledge the valuable input provided by the @vicky_ . Their initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue. 

You can try this.

ConditionalValue =
IF(
    SUM(Record[fines])
    <[TotalAvg],
    [TotalAvg]-([fines average per dc_nbr]),
  SUM(Record[fines])) 

More detailed information can be found in the pbix attachment.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vicky_]

The issue might be in the 1st part of your DAX:

ConditionalValue =
IF(
    SELECTEDVALUE(Record[fines]) ... // is this supposed to be SUM?

I think what's happening is that the SELECTEDVALUE() can't find a single value of [fines] per dc_nbr value, so it will return BLANK. The docs for the function (https://learn.microsoft.com/en-us/dax/selectedvalue-function-dax) says that it will only return one distinct value, otherwise it will return an alternative value, which defaults to blank (and will be less than whatever [TotalAvg] you have). As a result, the second condition never evaluates.

[Debsidrian]

yes is supposded to be a SUM