Forum Discussion

harib's avatar
harib
Post Patron
7 years ago
Solved

Average with if condition

Hello Friends,

 

Sample table here.  I need a average from Returns (Qty), Shipped (Qty) 

Returns (Qty)  :  3296

Shipped (Qty) :  18732408

Rate (%)          : 0.21%

 

Rate (%) =If [Shipped (Qty)]=0 Then 0 Else 12 * [Returns (Qty)] / [Shipped (Qty)] + 0

Can we get average with if Condition like above formula. 

 

Thanks in Advance

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    harib - The DIVIDE function has 3 parameters - Numerator, Denominator and Alternate Result. You could create a measure like this:

    Rate (%) = 
var num = SUM([Returns (Qty)]) * 12
var den = SUM([Shipped (Qty)])
return divide(num, den, 0)

    Hope this helps,

    Nathan

  • TeigeGao's avatar
    TeigeGao
    7 years ago

    Hi harib ,

    We can also use the IF() function.

    Rate (%) = IF ([Shipped (Qty)]=0 , 0 , 12 * DIVIDE([Returns (Qty)] ,[Shipped (Qty)] )+ 0)

    Best Regards,

    Teige

2 Replies

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    Hi harib ,

    We can also use the IF() function.

    Rate (%) = IF ([Shipped (Qty)]=0 , 0 , 12 * DIVIDE([Returns (Qty)] ,[Shipped (Qty)] )+ 0)

    Best Regards,

    Teige

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    harib - The DIVIDE function has 3 parameters - Numerator, Denominator and Alternate Result. You could create a measure like this:

    Rate (%) = 
var num = SUM([Returns (Qty)]) * 12
var den = SUM([Shipped (Qty)])
return divide(num, den, 0)

    Hope this helps,

    Nathan