Home Forums Power Pivot How calculate an average from a filed on related table??

Viewing 8 posts - 1 through 8 (of 8 total)
• Author
Posts
• #3979

Hello, I’m a bit new to DAX,

I have tried several methods but I can not get it

I have the following related tables as shown below

table1 (client, dto)

>> —–>

table2 (dto, area)

>> —–>

table3 (dto, value)

The goal is to get the average from the “value” field of table3 with a calculated field

Average_value: = CALCULATE (AVERAGEX (Table3, table3 [value]))

Does not mark any error creating the calculated field,
But when you add in the PivotTable, repeat the same value.
I would greatly appreciate your help to solve this

regards

#3980

From your description of results, it sounds like you are working with a many-to-many relationship.

Can you put together some sample data in a workbook?

#3982

but no, its not many to many relationship

Here is the example attached

What i need is the average from he field “Value” on tabla3

Thankyou very much

###### Attachments:
You must be logged in to view attached files.
#3985

Interesting.

You are either new to DAX (as said in the original post for this topic) or enjoy riddles.

Structurally, you have set up the relationship between tables 4, 2 and 3 as a many-to-many relationship where table 2 would function as the bridge table.

However, table 2 has a child table (table 1) and that parent-to-child (one to many) relationship between table 1 and table 2 enforces a unique constraint in the dto column of table 2. In turn, that uniqueness constraint on the dto column in table 2 requires the potential many-to-many between table 2 and table 3 behave more like a one-to-one relationship.

If this were a riddle and if I were clever, I would say to reverse the ordinality of the relationship between table 2 and table 3 (you can do that when a relationship behaves as one-to-one) and your formula for average will work.

However, since this site focuses on productivity, I will ignore the possibility of a riddle and say the fix is better model design. Since there is an enforced one-to-one relationship between tables 2 and 3, table 3 is unnecessary and the value column should be moved to table 2. Make this change in the model design and the average against the re-located value column in table 2 will work. Also, as a bonus, the average based on dropping client on pivot rows instead of areas will also work as expected.

Without improving your model, you will have to write a more complex formula that, for starters, could look like:

Average_Value_of_Area:=IF ( HASONEVALUE ( Tabla4[NomArea] ), CALCULATE(AVERAGE(Tabla3[Valor]), ‘Tabla2’ ) )

The above formula with a reference to table 2 as a bridge table evaluates correctly for the current model when working with single areas (not for calculating averages of sub-totals or grand totals).

#4008

Hello,
haha, well, yes I like puzzles, but this, I did not mean it was one.

I choose to correct the data model, thanks to your answer now can see a little more clearly how this works, well I think 🙂

I assumed that PowerPivot could easily identify one-one relationship, but it is not, I think.
Therefore, I believe, I can not carry out any operation of aggregation (sum, average, count) in a calculated field when the measure is in a lookup table in a relationship one to one while grouping by a field in another lookup table in a many-to-one.

This is because PowerPivot will see it as a many-to-many relationship?

It will be a limiting factor in PowerPivot?

regards

#4014

Hi,

None of these are limiting factors. As you get to know Power Pivot better they will all make sense.

If you do not have a copy of the free Power Pivot reference card created by Rob Collie and Avi Singh, here is the download link:

The first page that shows how the formula engine evaluates formulas can help you understand better how relationships work.

A paid resource that I recommend highly is the Comprehensive Power Pivot Course, which you can learn about here:

#4016

Hi OK!

#4316

OHHHH yess  you did that in the first answerr!! LOL

Solved with no changes at all:

Yes you rigth!!, No limitation in DAX, after a very hard learning hours

Average_value: = CALCULATE (AVERAGE (table3, table3[value]), table2 )

What do you thing??