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

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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

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

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