Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I
Helper I

Summarization with related tables

Hi together,


I have the following data-modell where I turned some n:m relationships into 1:n relationships with a relationship table:


The basic idea is, that a person has a numeric effort during a specific timeline.


Now I want to summarize all efforts from all persons.


I think I need a DAX-function for this, right? (I'm not familiar with DAX, sorry)


Which DAX-function do I need to make a summarization within related tables?


Thank you in advance!





Community Support
Community Support

Hi @AndreasSc ,


Please try this:

avg = CALCULATE(AVERAGE(Effort[Effort_value]), ALLEXCEPT(Person,Person[ID_Person]),ALLEXCEPT('Time','Time'[ID_Time]))

Or change the default summarization type to Average like this:

summarize type.jpg


For more details about DAX function reference, please follow the below official document:

Data Analysis Expressions (DAX) Reference - DAX | Microsoft Docs


Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-eqin-msft ,

unfortunately this messure doesn't work for me. I get some strange numbers.


Do you use the Primary Keys from the related tables within the ALLEXCEPT statement, right?





Helper I
Helper I

Thank you very much for this hint! It works with SUM now.


But I have another question regarding this topic:
My values are on a monthly basis.
If a user selects two months for example, the values are just summarized but actually it should calculate the average of both monts.

How do I determine the selected months to be able to do this calculation? Is there a DAX-function for it?


Thank you again.

Continued Contributor
Continued Contributor


I think you should let the Effort_Value denormalized in the table Person/Effort table. This way, you will be able to do just a regular SUM of the effort_value column

Helpful resources


Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.