Forum Discussion

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

Average by group ignoring Sub-Group

Hi,

 

I have a table of the form:

 

Id     Group   Sub-Group    value

1          A           1A                 10

2          A            2C                 50

3          B            1A                 12   

10        C            2A                 5

11        A           1B                  100

110      C             3C                 1000

n          xx            xxx                  nnnn

 

I built a barplot for AVG(value) by Sub-Group and I'd like to show the AVG(value) by Group as a line.

 

I tried to calculate the AVG(value) by Group as 

Avg value by Group=
CALCULATE(
    AVERAGE(
        Table[value]
    ),
    ALLEXCEPT(
        Table, Table[Group]
    )
)
 
But as you can see below (blue line) this is just same as Avg(value) by Subgroupm, while I would expect a straight line cutting the bars.

 

Any help on this?

Thx

 

 

Need Help
Tips and Tricks
  • Ritaf1983's avatar
    Ritaf1983
    1 year ago

    Hi uk-roberto92 
    You can use a dax measure :

    Group average = 
    VAR selectedGroup = SELECTEDVALUE('Table'[Group])
    VAR AVG_ =
    CALCULATE(
    AVERAGEX(
    SUMMARIZE('Table', 'Table'[Group], "Sum_", AVERAGE('Table'[Value])),
    [Sum_]
    ),
    ALLSELECTED('Table'[Sub-Group]),
    KEEPFILTERS('Table'[Group] = selectedGroup)
    )
    RETURN
    AVG_

     

    Important Notes:

    1. Tooltip Dependency for Sorting
      To sort the bars according to the group, you need to add the Group field into the Tooltips section — as shown in the image.
      This ensures the "Group average" line aligns with each bar correctly by maintaining group context.

    2. Granularity Mismatch – Line May Disappear
    Due to the granularity difference between the Sub-Group (X-axis) and Group average (aggregated by Group), the line won’t appear for the first bar unless you structure your visual hierarchically — like in the second image where the hierarchy is visualized.

    3. 

    Visual Misinterpretation – Lines Suggest Trends
    A line chart (or combo chart with a line) is typically interpreted by users as a trend over time.
    Since this visual is not time-based, to avoid misleading your users, I recommend replacing the line with just markers, like in the third image.
    This keeps the message clear — you're showing averages per group, not a time-based trend.

    Alternatively, you can:

    • Show a single visual with the overall average,

     

    • And create a second chart showing the delta from group average.

    The pbix is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

3 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    1 year ago

    Hi uk-roberto92 
    You can use a dax measure :

    Group average = 
    VAR selectedGroup = SELECTEDVALUE('Table'[Group])
    VAR AVG_ =
    CALCULATE(
    AVERAGEX(
    SUMMARIZE('Table', 'Table'[Group], "Sum_", AVERAGE('Table'[Value])),
    [Sum_]
    ),
    ALLSELECTED('Table'[Sub-Group]),
    KEEPFILTERS('Table'[Group] = selectedGroup)
    )
    RETURN
    AVG_

     

    Important Notes:

    1. Tooltip Dependency for Sorting
      To sort the bars according to the group, you need to add the Group field into the Tooltips section — as shown in the image.
      This ensures the "Group average" line aligns with each bar correctly by maintaining group context.

    2. Granularity Mismatch – Line May Disappear
    Due to the granularity difference between the Sub-Group (X-axis) and Group average (aggregated by Group), the line won’t appear for the first bar unless you structure your visual hierarchically — like in the second image where the hierarchy is visualized.

    3. 

    Visual Misinterpretation – Lines Suggest Trends
    A line chart (or combo chart with a line) is typically interpreted by users as a trend over time.
    Since this visual is not time-based, to avoid misleading your users, I recommend replacing the line with just markers, like in the third image.
    This keeps the message clear — you're showing averages per group, not a time-based trend.

    Alternatively, you can:

    • Show a single visual with the overall average,

     

    • And create a second chart showing the delta from group average.

    The pbix is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi uk-roberto92,

    As we haven’t heard back from you, we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
    If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

     

    Regards,
    Vinay Pabbu