How to make Ratio display 100 when AVE_AMT = 0 ?

[Anonymous]

Hi All

My below expression working fine :-

_RATIO = DIVIDE (INVC[Amt_On Hand],INVC[AVE_AMT])

May i know how to make _RATIO display 100 when AVE_AMT = 0 ?

Now it display Null value.

My PBI file :-

https://www.dropbox.com/s/kp4a08blus6r8ay/PBT_SAMPLE%20100%20ROW%20JAN%202021%20V005.pbix?dl=0

Paul Yeo 

[amitchandak · Accepted solution]

@Anonymous ,

try like

DIVIDE (INVC[Amt_On Hand],INVC[AVE_AMT],1)

or

DIVIDE (INVC[Amt_On Hand],INVC[AVE_AMT],100)

or

if(INVC[AVE_AMT] =0, 1,DIVIDE (INVC[Amt_On Hand],INVC[AVE_AMT]))

or

if(INVC[AVE_AMT] =0, 100,DIVIDE (INVC[Amt_On Hand],INVC[AVE_AMT]))

[MFelix]

Hi @Anonymous ,

Theres is an optional argument on the divide function that is alternate result that is given when you have a null or error in this case change your measure to:

_RATIO = DIVIDE (INVC[Amt_On Hand];INVC[AVE_AMT]; 100)