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

This topic contains 7 replies, has 2 voices, and was last updated by  amarines 4 years, 9 months ago.

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

    amarines
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    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

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    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:

    A mystifying and awesome solution for many 2 many

    Can you put together some sample data in a workbook?

    #3982

    amarines
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    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

     

     

     

     

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

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    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

    amarines
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    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

    #4014

    tomallan
    Keymaster
    • Started: 0
    • Replies: 417
    • Total: 417

    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:

    Self Paced Online Video Training

    #4016

    amarines
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    Hi OK!

    thanks you for the link

    Regrads

    #4316

    amarines
    Participant
    • Started: 1
    • Replies: 4
    • Total: 5

    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!!

     

     

     

Viewing 8 posts - 1 through 8 (of 8 total)

The forum ‘Power Pivot’ is closed to new topics and replies.