Average by group ignoring Sub-Group

[uk-roberto92]

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

[Anonymous]

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

[Ritaf1983 · Accepted solution]

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

[uk-roberto92]

Hi,

sorry I must have duplicated this post by mistake... I eventually went by the code proposed in https://community.fabric.microsoft.com/t5/Desktop/Average-by-group-ignoring-sub-groups/m-p/4728951, which is something similar to your proposal. 

However just tried your solution and this works as well!

Thanks a lot