Forum Discussion

uk-roberto92's avatar
uk-roberto92
Frequent Visitor
1 year ago
Solved

Average by group ignoring sub-groups

Hi,   I have a table of the form Id     Group    SubGroup    Value 1         A              1a             10 2         A               3a               1 3         B               2c          ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

johnt75's avatar
johnt75
Super User
1 year ago

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