Search code examples
powerbidaxpowerbi-datasource

how not to display YOY percentage change if its -100% Power BI

I have a Stalked Column Chart that displays 12 months Year over Year comparison.

If I look at the data using table - then it looks fine, we have value for January 2018 only, and the rest are blank.

enter image description here

But if I put it on a chart - it shows negative 100%.

If I do not have Premium value in my data for current year, then the chart displays -100%.

So how can I NOT display negative 100 % if premium for current year is blank but still displays Months name?

This is the measure for calculation:

YOY Percent Change = DIVIDE(Premiums[Total Premium],Premiums[TotalPremium LastYear],0)-1

enter image description here

Solution

  • Try using the ISBLANK() function to test the availability of data :

    YOY Percent Change = 
   IF( ISBLANK(Premiums[Total Premium])
     , 0
     , DIVIDE( Premiums[Total Premium]
           , Premiums[TotalPremium LastYear]
           , 0 )
       -1
     )