Forum Discussion

salonikhedkar_1's avatar
salonikhedkar_1
Icon for Helper II rankHelper II
6 years ago
Solved

Disruption due to formula

When we select "Show items with no data", a blank row appears. How to fill the blank row with 0 ?

 

I have tried this formula:

if(isblank(calculate(sum(amount), sameperiodlastyear(date))),0,calculate(sum(amount), sameperiodlastyear(date))))

 

But this formula disrupts other column which is driven by an IF statememt:

if(Country = "United States of America" && month(date)>=1 && month(date)<=3, 100,

If(Country = "United States of America" && month(date)>=4 && month(date)<=6, 200,

if(Country = "United States of America" && month(date)>=7 && month(date)<=9, 300,

if(Country = "United States of America" && month(date)>=10 && month(date)<=12, 400)

 

it is adding extra 0's in the visual.

 

I am totally confused on why the first formula is caused such big issues.

 

Appreciate your quick responses.

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi salonikhedkar_1 ，

     

    Modify your measure as below:

     

    if(Country = "United States of America" && month(date)>=1 && month(date)<=3, 100,
If(Country = "United States of America" && month(date)>=4 && month(date)<=6, 200,
if(Country = "United States of America" && month(date)>=7 && month(date)<=9, 300,
if(Country = "United States of America" && month(date)>=10 && month(date)<=12, 400,0)

     

    Add "0" at the end of the last if statement,otherwise it will return blank for other criteria.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

3 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi salonikhedkar_1 ，

     

    Modify your measure as below:

     

    if(Country = "United States of America" && month(date)>=1 && month(date)<=3, 100,
If(Country = "United States of America" && month(date)>=4 && month(date)<=6, 200,
if(Country = "United States of America" && month(date)>=7 && month(date)<=9, 300,
if(Country = "United States of America" && month(date)>=10 && month(date)<=12, 400,0)

     

    Add "0" at the end of the last if statement,otherwise it will return blank for other criteria.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!