Summarizing within a VAR expression

[Anonymous]

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	Matrixcode	Average Revenue	Matrixcode average
1	A	10	8
2	B	8	8
3	A	6	8

I used this formula to get the column 'Matrixcode average' in the table above:

Matrixcode average =

VAR __MatrixCode = MAX('Table'[Matrixcode])

VAR __Table = FILTER(ALL('Table'),[Matrixcode] = __MatrixCode)

VAR __Result = AVERAGEX(__Table, [Average Revenue)

RETURN

__Result

This worked well!

In a new (but similar) set, with an extra column (Productcategory), this new column causes that the store column is now repeated.

Store	Matrixcode	Productcategory	Average Revenue	Matrixcode average
1	A	Milk	8	17,2
1	A	Eggs	12	17,2
2	B	Bread	1	3,5
2	B	Milk	6	3,5
3	A	Cheese	20	17,2
3	A	Milk	16	17,2
3	A	Bread	30	17,2

The issue is that the formula I have averages every single row value.
Example: For stores with matrixcode value A, it add: (8 +12 + 20 + 16 + 30)/5 = 17,2.
What I now want to formula to do is: sum each stores total revenue, and make an average of it.
So: Store 1 = 20, store 3 = 66. The average should get to (20 + 66) / 2 = 43.

How do I alter the formula below to make it so? Is that even possible within this formula?

Matrixcode average =

VAR __MatrixCode = MAX('Table'[Matrixcode])

VAR __Table = FILTER(ALL('Table'),[Matrixcode] = __MatrixCode)

VAR __Result = AVERAGEX(__Table, [Average Revenue)

RETURN

__Result

Many thanks for anyone still with me at this point. Any help is greatly appreciated 🙂
Thanks, 

Frans

[tamerj1]

Hi @Anonymous 

please try

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

[Anonymous]

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

[smpa01 · Accepted solution]

@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]

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