Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Summarizing within a VAR expression

Hi all, I'm struggling to find the answer, so hopefully someone here can help me. I feel like it's an easy fix within DAX for a trained eye. 😉 This is an example set I had. Store Matrix...
  • smpa01's avatar
    smpa01
    3 years ago

    Anonymous 

    Measure = 
VAR numerator =
    CALCULATE (
        SUMX (
            ALL ( 'table'[Store] ),
            CALCULATE (
                SUM ( 'table'[Average Revenue] ),
                ALLEXCEPT ( 'table', 'table'[Store], 'table'[Matrixcode] )
            )
        )
    )
VAR denominator =
    CALCULATE (
        DISTINCTCOUNT ( 'table'[Store] ),
        ALLEXCEPT ( 'table', 'table'[Matrixcode] )
    )
RETURN
    DIVIDE ( numerator, denominator )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Anonymous 

please try

Matrixcode average =
AVERAGEX (
CALCULATETABLE (
'Table',
ALLEXCEPT ( 'Table', 'Table'[Store], 'Table'[Matrixcode] )
),
[Average Revenue]
)

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thank you for replying!
This unfortunately did not succeed, it resulted in similar results.