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.StoreMatrixcodeAverage RevenueMatrixcode average1A1082B883A68I 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__ResultThis worked well!In a new (but similar) set, with an extra column (Productcategory), this new column causes that the store column is now repeated.StoreMatrixcodeProductcategoryAverage RevenueMatrixcode average1AMilk817,21AEggs1217,22BBread13,52BMilk63,53ACheese2017,23AMilk1617,23ABread3017,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__ResultMany thanks for anyone still with me at this point. Any help is greatly appreciated 🙂Thanks, Frans

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

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 )

Forum Discussion

Summarizing within a VAR expression

4 Replies

Related content

Parameter in summarize

Don't Summarize....still summarizes...?

GroupBY and summarize

Help!!! Expression Error

Regular expression