Forum Discussion

RodrigoTXRA's avatar
RodrigoTXRA
Icon for Helper I rankHelper I
8 years ago
Solved

Dax formula - dealing with zero amounts

Hi All Hope someone can shed some light on this formula below. Long story short, it works well as long as all the items contains some type of value, as soon as one of the items is ZERO, this formu...
  • Anonymous's avatar
    Anonymous
    8 years ago

    Here it is...

     

    PERFORMANCE WINS =IF( ISBLANK([SUM OF WINS]),0,[SUM OF WINS])/ IF( ISBLANK([SUM OF TARGET WINS]),0,[SUM OF TARGET WINS])  *100

     

    I have replaced the blank by 0, however you can change with your desired value.

     

    Thanks
    Raj

RodrigoTXRA's avatar
RodrigoTXRA
Icon for Helper I rankHelper I
8 years ago

Hi All

 

Just wondering whether someone can help me. 

I have this formula below which divides de KPIs by 5, but whenever one of the KPI is empty or have ZERO as the value, this formula fails to calculate properly.

 

PERFORMANCE KPI % = (
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE ACC REVIEWS] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE FEEDBACK/MI] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE WINS] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE QA] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE MI] +
'KPI_DATA - FROM EXCEL EXTRACT'[PERFORMANCE STAKEHOLDER ENGAGEMENT])/5

Appreciate any kind of assistance

Regards

Rodrigo

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    8 years ago

    I cannot replicate the issue - for me 0/null values are aggregated properly in the calculated column
    are you sure there are no text values in the empty cells?