Forum Discussion

AndyTrezise's avatar
AndyTrezise
Icon for Advocate IV rankAdvocate IV
8 years ago
Solved

Multiplying across numerous tables

My data a model that includes three tables which are all linked as follows:

 

Projects -> Tasks -> Assignments

 

Multiply Tasks per project, multiple assignments per task.

 

I need to arrive at a value which is the result of multiplying a filed from each table – Projects.Column1 * Tasks.Column1 * Assignments.Column1

 

I’ve managed to do it by creating new columns using LOOKUPVALUE so all the values are in the same table – I can then create a further column to multiply them out.

 

However I’m sure there must be a single step way of multiplying these value across the 3 tables??

Need Help
  • spuder's avatar
    spuder
    8 years ago

    Hi AndyTrezise

    I'm not absolutely sure. But you could try to use

    =SUMX(PROJECTS;'Projects'[Number]*RELATED('Tasks'[Number]*RELATED('Assignments'[Number]))

     

     

    Note: This is only vailid when ONE Project has ONE Task and ONE Assignement. In fact 1:1 relationship.

     

    Greetings spuder

  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi  spuder

     

    It should be possible to multiply across three tables without using the LOOKUPVALUE column.

     

    If you create a calculated column in your Assignments table you can use a calculation like this

     

    Column = RELATED('Projects'[Val]) * RELATED('Tasks'[Val]) * Assignments[Val]

    This can also be done using a measure, or as a column in one of the higher tables, but you'd need to use RELATEDTABLE and SUM to complete.

     

     

3 Replies

  • spuder's avatar
    spuder
    Icon for Resolver IV rankResolver IV
    8 years ago

    Hi AndyTrezise

    I'm not absolutely sure. But you could try to use

    =SUMX(PROJECTS;'Projects'[Number]*RELATED('Tasks'[Number]*RELATED('Assignments'[Number]))

     

     

    Note: This is only vailid when ONE Project has ONE Task and ONE Assignement. In fact 1:1 relationship.

     

    Greetings spuder

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi  spuder

     

    It should be possible to multiply across three tables without using the LOOKUPVALUE column.

     

    If you create a calculated column in your Assignments table you can use a calculation like this

     

    Column = RELATED('Projects'[Val]) * RELATED('Tasks'[Val]) * Assignments[Val]

    This can also be done using a measure, or as a column in one of the higher tables, but you'd need to use RELATEDTABLE and SUM to complete.