Summarization with related tables

[Anonymous]

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!

Regards,

Andreas

[Anonymous]

Hi @Anonymous ,

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:

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.

[Anonymous]

Hi @Anonymous ,

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?

Regards,

Andreas

[Anonymous]

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.

[m3tr01d]

Hi,

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