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

[Anonymous]

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

[sjoerdvn]

The table expression in that SUMX should yield the same rows as your visual, so probably something like VALUES(datetable[yearmonth]). Also @xifeng_L is correct in that you can't use variables like that. One way to fix that is to create sepperate measures similar to the variables, and use the measures inside the SUMX expression. 

[xifeng_L · Accepted solution]

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~

[Greg_Deckler]

@Anonymous You could look at this: (2) Better Weighted Average per Category - Microsoft Fabric Community

Also, for the total, first, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8