Forum Discussion

bassmaninaus_PB's avatar
bassmaninaus_PB
Frequent Visitor
4 years ago
Solved

DAX Weighted Average using Divide without calculating zeros

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's avatar
    amitchandak
    4 years ago

    bassmaninaus_PB , simply try like this

     


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

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    bassmaninaus_PB , simply try like this

     


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