Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

SUMX with Calculated() - Weighted average can't be calculated

Hello all,

 

so for some context, i'm trying to calculate a forecast accuracy with weighted average, meaning when it's all summed up, i want a value that is influenced by how much weight each row has.

For the calculation i need to access different key figures, which are defined in a single row called "key figure". For the calculation of the Forecast accuracy i can just use some calculate formulas and it works perfectly fine.

Problem is, when i try to make the normal forecast accuracy weighted. I tried to do this with SUMX, but it somehow gives me values that are not possible. I put much time in research and couldn't find anything. I assume that with the SUMX, some filter context of the defined variables get lost (not sure though).

Anyway, here's the original DAX formula for the forecast accuracy (not weighted yet). it works as intended:

 result of this formula. on single row basis this works, but the total (yellow) is not weighted, which is what i try to achieve:

 

 

So here is the new dax formula, that i created to get the forecast accuracy to be weighted:

The result is not possible. On single row bases, the value need to be identical to the above ones, but they aren't. The total doesn't make any sense at all:

 

I was pretty confident that the formula makes sense, but i'm probably missing something. Is this entire thing not possible, because the key figures are in one single colums, just like the values (volume cs) are in the same column?

Thanks! 😃

Best regards
Nicolas




 

 



DAX
power bi
sumx
weighted average
  • xifeng_L's avatar
    xifeng_L
    2 years ago

    Hi Anonymous 

     

    This is because variables are only calculated once at the time of definition, and are not calculated again in any subsequent references.

     

    Accourd to your need, you can try below measure.

     

    FCA M-2 =
VAR TempTbl =
    GENERATE (
        CROSSJOIN (   // if not multiple row field then only use the VALUES()
            VALUES ( Table[Row Field 1 in Matrix] ),
            VALUES ( Table[Row Field 2 in Matrix] )
        ),
        VAR ConsensusQty =
            CALCULATE ( SUM ( Table[Volume CS] ), Table[Key Figure] = "Consensus...." )
        VAR ActualQty =
            CALCULATE ( SUM ( Table[Volume CS] ), Table[Key Figure] = "Actual...." )
        VAR FCA =
            1 - ABS ( DIVIDE ( ConsensusQty - ActualQty, ConsensusQty ) )
        RETURN
            ROW ( "ConsensusQty", ConsensusQty, "ActualQty", ActualQty, "FCA", FCA )
    )
RETURN
    DIVIDE ( 
        SUMX ( TempTbl, [FCA] * [ActualQty] ), 
        SUMX ( TempTbl, [ActualQty] ) 
    )

     

     

    Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

     

    Thank you~

3 Replies