This topic contains 7 replies, has 2 voices, and was last updated by 
-
Posts
-
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 thisregards
From your description of results, it sounds like you are working with a many-to-many relationship.
To see a discussion of many to many, please follow this link:
Can you put together some sample data in a workbook?
Hello Thanks for your answer
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
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).
Hello,
haha, well, yes I like puzzles, but this, I did not mean it was one.Thank you for your correct answer,
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
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:
http://www.powerpivotpro.com/wp-content/uploads/2015/10/PowerPivotPro-Reference-Card.pdf
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:
Hi OK!
thanks you for the link
Regrads
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??
Ragrads!!
The forum ‘Power Pivot’ is closed to new topics and replies.