DAX help on allexcept

[Anonymous]

my goal is get the last two columns (avg per subgroup and avg per group) in the pivot table by using a measure instead of a calculated column

ID	VALUE	SUBGROUP	GROUP	AVG PER SUBGROUP	AVG PER GROUP
1	20	A	1	20	27.5
2	10	B	2	30	30
3	50	B	2	30	30
4	30	B	2	30	30
5	35	C	1	35	27.5

I manage to do this using calculated column 

AVERAGE PER SUBGROUP = CALCULATE(AVERAGE(Sheet2[VALUE]),ALLEXCEPT(Sheet2,Sheet2[SUBGROUP]))

However when i tried to do this in a measure it calculates all the rows. 

what changes should i put in the measure to consider also the row context in the pivot table 

[Nathaniel_C]

Hi @Anonymous ,

If I understood what you are looking for in your post.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Average of subgroup =
VAR _subGroup =
    MAX ( 'Table'[SUBGROUP] )
VAR _groupcount =
    CALCULATE (
        [Count of Value],
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[SUBGROUP] ),
            MAX ( 'Table'[SUBGROUP] ) = _subGroup
        )
    )
VAR _calc =
    CALCULATE (
        [Sum of Value],
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[SUBGROUP] ),
            MAX ( 'Table'[SUBGROUP] ) = _subGroup
        )
    )
VAR _average =
    DIVIDE ( _calc, _groupcount )
RETURN
    _average
-----------------------------------------

Average of group = 
VAR _Group =
    MAX ( 'Table'[GROUP] )
VAR _groupcount =
    CALCULATE (
        [Count of Value],
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[GROUP] ),
            MAX ( 'Table'[GROUP] ) = _Group
        )
    )
VAR _calc =
    CALCULATE (
        [Sum of Value],
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[GROUP] ),
            MAX ( 'Table'[GROUP] ) = _Group
        )
    )
VAR _average =
    DIVIDE ( _calc, _groupcount )
RETURN
    _average

[Anonymous]

Hi @Nathaniel_C 

To further complicate my example, kindly refer to the revised fact table 

ID	VALUE	SUBGROUP	GROUP
1	20	A	1
2	10	B	2
3	50	B	2
4	30	B	2
5	35	C	1
6		B	2
7		D	2

IDs 6 and 7 does not have values. My endgoal actually, is to provide values for these IDs using the average values derived in either subgroup or group. Particularly for ID 6 since it belongs to the same subgroup B, it will use the average value for subgroup B which is 30. Meanwhile for ID 7, since we do not have an average value for subgroup D, we will use the the average value for group 2, which is 30 also. Hence, im quite fascinated in the use of VAR since the actual fact data that i will using contains several thousand of rows and i fear making to iteration the average calculation will make the dax process inefficient. 

[Anonymous]

Hi @Nathaniel_C 

Thank you for the immediate reply, can clarify just to understand the measure? i'm quite new in DAX hence please bear with me in following your suggested measure.

1. The average was derived by getting the sum for each of the group divided by the number of rows for each group. My question is what does 

 MAX ( 'Table'[SUBGROUP] 

return? 

2. in this particular expression

MAX ( 'Table'[SUBGROUP] ) = _subGroup

what is the difference between the two?