Forum Discussion

rashmij9's avatar
rashmij9
Frequent Visitor
3 years ago
Solved

Replace blanks with 0 in measure for dynamic selection

I have a dynamic measure that helps display either quantity or revenue and changes numbers based on the slicer selection.

 

I use the following measure to calculate the revenue/quantity:

 

SelectedMeasure =
VAR quantity = CALCULATE(SUM('Main Data'[quantity])/1000000)
VAR quantitywithzero = IF(ISBLANK(quantity),0,quantity)

VAR revenue = CALCULATE(SUM('Main Data'[Revenue])/1000000)
VAR revenuewithzero = IF(ISBLANK(revenue),0,revenue)

RETURN IF(HASONEVALUE('Measure Table'[Column1]), SWITCH(VALUES('Measure Table'[Column1]), "Quantity (M units)", quantitywithzero, "Revenue ($M)", revenuewithzero, quantitywithzero))
 
Currently, in my matrix, I wish to replace all blank values with 0 but I have been having trouble incorporating this in my current measure.
Appreciate any suggestions!
Need Help

1 Reply

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago

    You need to add zero to the measure like this
    +0

    if that doesn't help, then read about Auto Exist
    https://youtu.be/AZBGRo3FJ5c?t=219
    do the same as in the video  creat dimesion table, and then try again add zero already