Forum Discussion

Debsidrian's avatar
Debsidrian
Frequent Visitor
2 years ago
Solved

HELP with DAX (Conditional Measure)!!!!

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:    C...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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_'s avatar
vicky_
Super User
2 years ago

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's avatar
    Debsidrian
    Frequent Visitor
    2 years ago

    yes is supposded to be a SUM