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 )
smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
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 )
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Perfect, this worked! Thanks for the reply. I will study the formula to fully understand what you've written 🙂