Ignore blanks in Grand Total calculation

[trk26]

Hello,

I am trying to divide the cost of injectables by pigs produced by group to get to an injectable cost per pig produced.  Some groups will not have injectables as it is N/A to them; thus, it is blank in the data.  All groups will have pigs produced.  I've been able to accurately calculate the cost per pig produced, but the grand total is not calculating properly.  How should I update my formula below so it ignores the groups that do not have injectable amounts (i.e. blanks)?  I believe the total in the example below should be $0.1356 not $0.0505.  Note, the formula below was written with the intention that the result is only shown at the "Yr-Wk-Site" level as well as the Total level.  Thank you.

Inject / pig =

IF (

(

ISFILTERED ( MetaFarms_FinishGroups[Yr-Wk-Site] )

&& NOT ( ISFILTERED ( MetaFarms_FinishGroups[Producer] ) )

),

DIVIDE (

AVERAGEX ( 'Nursery Drug Costs', 'Nursery Drug Costs'[Injectables Cost] ),

CALCULATE ( SUM ( MetaFarms_FinishGroups[PigsProduced] ) ),

BLANK ()

)

)

Thanks,

[gckcmc]

try:

Result =
AVERAGEX ( FILTER ( Table, Table[Value] <> BLANK () ), Table[Value] )

Inject / pig =

IF (

(

ISFILTERED ( MetaFarms_FinishGroups[Yr-Wk-Site] )

&& NOT ( ISFILTERED ( MetaFarms_FinishGroups[Producer] ) )

),

DIVIDE (

AVERAGEX ( 'Nursery Drug Costs', 'Nursery Drug Costs'[Injectables Cost] <> BLANK() ),

CALCULATE ( SUM ( MetaFarms_FinishGroups[PigsProduced] ) ),

BLANK ()

)

)

[trk26]

Thank you for the suggestion.  After I tried your suggestion, I received the following calculation error message, " The function AVERAGEX cannot work with values of type Boolean."