Total Average with 0% values

[powerbiadmin123]

Hello Everyone, 

I'm trying to find the total average of my fill rate percentages. This is the measure used for my fill rate; 

Fill Rate = (IF(DIVIDE([SumF64],[SumOrderQty])>1,1,DIVIDE([SumF64],[SumOrderQty]))).

My problem is that when creating an average measure it works fine until 0% are included, I understand this happens since the zero values are not included when using the if function in the fill rate measure. How could I create an average function that includes all my values even when being zero?

[amitchandak]

@powerbiadmin123 , Try like

Fill Rate = (IF(DIVIDE([SumF64],[SumOrderQty],0)>1,1,DIVIDE([SumF64],[SumOrderQty],0))).

[powerbiadmin123]

HI!

Thank you for your reply! However, I am still having the same issue when calculating my average, this is my measure; 

AVG Fill Rate =

VAR __Table =

ADDCOLUMNS(

'REC_HDR_Append',

"__Fill Rate",[Fill Rate 2 ]

)

RETURN

AVERAGEX(__Table,[Fill Rate 2])

[yingyinr]

Hi @powerbiadmin123 ,

Could you please provide more sample data with Text format and the formula of SumF64 and SumOrderQty in order to make troubleshooting? I just tried to reproce your problem,but it failed.... I can get the correct average value, please check the below screenshot and the attachment for more details.

Best Regards