Replacing blank with 0 in calculated column?

[enorberg]

My solution for replacing blanks with 0 in a measure is normally:

Example measure = var example =
CALCULATE(.......)
return
IF(ISBLANK(example),0,example)

For some reason this doesn't seem to work on a calculated column, is that right? Is there a way round it? Mine consists of a large number of IF statements, where, if all are FALSE, the value should be 0 rather than blank.

[enorberg]

Thanks all for trying to help,but  I still haven't managed to figure out out. My calculated column is pretty messy as I'm still a Power BI novice, but at least does the trick (....other than replacing (blank) with 0):

Weighted MRCM =

IF(Company[Current Pipeline Step] == "1. In progress" && NOT(ISBLANK(RELATED('Sales rep win probability'[Win probability 1]))), Company[mrcm] * RELATED('Sales rep win probability'[Win probability 1]),

IF(Company[Current Pipeline Step] == "1. In progress" && ISBLANK(RELATED('Sales rep win probability'[Win probability 1])), Company[mrcm] * RELATED('Team win probability'[Win probability 1]),

IF(Company[Current Pipeline Step] == "2. Connected" && NOT(ISBLANK(RELATED('Sales rep win probability'[Win probability 2]))), Company[mrcm] * RELATED('Sales rep win probability'[Win probability 2]),

IF(Company[Current Pipeline Step] == "2. Connected" && ISBLANK(RELATED('Sales rep win probability'[Win probability 2])), Company[mrcm] * RELATED('Team win probability'[Win probability 2]),

IF(Company[Current Pipeline Step] == "3. Problem Education Meeting Booked" && NOT(ISBLANK(RELATED('Sales rep win probability'[Win probability 3]))), Company[mrcm] * RELATED('Sales rep win probability'[Win probability 3]),

IF(Company[Current Pipeline Step] == "3. Problem Education Meeting Booked" && ISBLANK(RELATED('Sales rep win probability'[Win probability 3])), Company[mrcm] * RELATED('Team win probability'[Win probability 3]),

IF(Company[Current Pipeline Step] == "4. Problem Education Meeting Happened" && NOT(ISBLANK(RELATED('Sales rep win probability'[Win probability 4]))), Company[mrcm] * RELATED('Sales rep win probability'[Win probability 4]),

IF(Company[Current Pipeline Step] == "4. Problem Education Meeting Happened" && ISBLANK(RELATED('Sales rep win probability'[Win probability 4])), Company[mrcm] * RELATED('Team win probability'[Win probability 4]),

IF(Company[Current Pipeline Step] == "5. Solution Selection" && NOT(ISBLANK(RELATED('Sales rep win probability'[Win probability 5]))), Company[mrcm] * RELATED('Sales rep win probability'[Win probability 5]),

IF(Company[Current Pipeline Step] == "5. Solution Selection" && ISBLANK(RELATED('Sales rep win probability'[Win probability 5])), Company[mrcm] * RELATED('Team win probability'[Win probability 5]),

IF(Company[Current Pipeline Step] == "6. Justify" && NOT(ISBLANK(RELATED('Sales rep win probability'[Win probability 6]))), Company[mrcm] * RELATED('Sales rep win probability'[Win probability 6]),

IF(Company[Current Pipeline Step] == "6. Justify" && ISBLANK(RELATED('Sales rep win probability'[Win probability 6])), Company[mrcm] * RELATED('Team win probability'[Win probability 6]),

IF(Company[Current Pipeline Step] == "7. Decide" && NOT(ISBLANK(RELATED('Sales rep win probability'[Win probability 7]))), Company[mrcm] * RELATED('Sales rep win probability'[Win probability 7]),

IF(Company[Current Pipeline Step] == "7. Decide" && ISBLANK(RELATED('Sales rep win probability'[Win probability 7])), Company[mrcm] * RELATED('Team win probability'[Win probability 7]),

IF(Company[Current Pipeline Step] == "8. Verbal Commit" && NOT(ISBLANK(RELATED('Sales rep win probability'[Win probability 8]))), Company[mrcm] * RELATED('Sales rep win probability'[Win probability 8]),

IF(Company[Current Pipeline Step] == "8. Verbal Commit" && ISBLANK(RELATED('Sales rep win probability'[Win probability 8])), Company[mrcm] * RELATED('Team win probability'[Win probability 8]),

IF(Company[Current Pipeline Step] == "9. Won", Company[mrcm],

0

)))))))))))))))))

What it does it taking a sales rep's individual hit rate in a particulat sales stage, and multiplying it with the sales amount. When the individual rate is blank for whatever reason - perhaps the person is new - it takes the team's rate instead. The result is a sales amount forecast.

[az38]

Hi @enorberg 

does

RETURN 
example

returns you a correct result?

In other words, are you sure your CALCULATE() is correct?

check also data type for your column. try text data type and quotas

CALCULATE(.......)
return
IF(ISBLANK(example),"0",example)

[Pragati11]

Hi @enorberg ,

Not sure on your DAX expression as you haven't shared it. But, you can do something like this.

IF(NOT(ISBLANK(example)), CALCULATE(SUM(salesValue)), 0)

You can modify the above dax with your column names and logic.

Thanks,

Pragati

[parry2k]

@enorberg It should work, I don't see the reason why it wouldn't. If you have nested ifs you need to check your logic, the best would be to change if statement to SWTICH which is easy to read and maintain especially when there are many nested ifs.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡