DAX Weighted Average using Divide without calculating zeros

[bassmaninaus_PB]

Hi, I'm using the following DAX to calculate the weighted average of two columns. I have a large matrix table and using the below formula on several columns and just changing the values range each time.

18 Onshore Temps NHT = 
IF( Divide (
        SUMX( OpsLeaders, [6 Onshore Queued Volumes] * [18 Onshore Temps] ),
        SUMX( OpsLeaders, [6 Onshore Queued Volumes] )) 
= BLANK(), 0,
    Divide (
        SUMX( OpsLeaders, [6 Onshore Queued Volumes] * [18 Onshore Temps] ),
        SUMX( OpsLeaders, [6 Onshore Queued Volumes] )))

This formula works well for columns where there is data for each day in both the weighting & values columns, but some values columns are zero/don't have data each day, so the weighting is alot lower I assume because it is including zeros.

How can I adjust the above formula so it doesn't calculate zeros?

[amitchandak · Accepted solution]

@bassmaninaus_PB , simply try like this

Divide (
SUMX( OpsLeaders, [6 Onshore Queued Volumes] * [18 Onshore Temps] ),
SUMX( OpsLeaders, [6 Onshore Queued Volumes] ),0)