Average by group ignoring sub-groups

[uk-roberto92]

Hi,

I have a table of the form

Id     Group    SubGroup    Value

1         A              1a             10

2         A               3a               1

3         B               2c              100

4        A               1a                4

10       B               1a               80

11       B               2c                1000

43      C                1b               2

nn       xx               yy              aaa

I have the AVG(value) by subgroup as barplot and I'd like to add a line showing AVG(value) by Group.

I tried with the measure:

CALCULATE(

    AVERAGE(

        Table[value]

    ),

    ALLEXCEPT(

        Table, Table[Group]

    )

)

but this is just  showing the avg(value) by subgroup as well, below.

I would rather expect a straight line cutting through the subgroups...

Any help on this?

Thx

[Anonymous · Accepted solution]

Hi @uk-roberto92 ,
Thank you for reaching out to the Microsoft fabric community forum. Also, thanks @johnt75 , for those valuable insights for this thread.

I tried to recreate it on my local with the sample data.Please follow the below steps:
1.Create a measure for Bar (per SubGroup) using below:

AvgValueBySubGroup = AVERAGE('Table'[Value])

2.Create a measure for Line using below:

AvgGroupValueForLine =

VAR CurrentGroup = SELECTEDVALUE('Table'[Group])

RETURN

    CALCULATE(

        AVERAGE('Table'[Value]),

        REMOVEFILTERS('Table'[SubGroup]), 

        FILTER(

            ALL('Table'),

            'Table'[Group] = CurrentGroup

        )

    )

3. Use a Line and Clustered Column Chart and place Group and SubGroup as a hierarchy on the X-Axis. Add AvgValueBySubGroup to Column Values , and add AvgGroupValueForLine to Line Values to display a flat line representing the Group-level average across its SubGroups.

Please refer the sceenshot and file for your reference:

I hope this answer meets your requirements.If so, give us kudos and consider acceptin git as solution. If still facing any issue in resolving the issue, feel free to reachout!



Regards,
Pallavi G.

[uk-roberto92]

Hey @Anonymous thanks a lot, I was stuck on this for last 2 days and with the measure and the hierarchy it's working exactly as expected!

[johnt75]

ALLEXCEPT will not work in this instance because there is no explicit filter on the group column.

Try

Avg by group =
CALCULATE (
    AVERAGE ( 'Table'[Value] ),
    REMOVEFILTERS ( 'Table' ),
    VALUES ( 'Table'[Group] )
)