Forum Discussion

2NV_DB's avatar
2NV_DB
Frequent Visitor
2 years ago
Solved

Measure for data over multiple tables with multiple dimensions

Hi All,

I'm not sure how to articulate this correctly, but here goes:
I have the following tables and essentially I would like to build a measure where I am summing all the Sales vales in the sales table and multiplying by the multiplier for the category.

 

So in the above case, for customer 400029, Dress should be equal to (1500*0 + 2000*0 + 100*0)=0

Thank you

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi 2NV_DB ,

    Based on my testing, please try the following methods:

    1.Create the sample table.

    2.Create the relationships between three tables.

     

    3.Create the new measure to calculate sales values.

    Sales with Multiplier = 
SUMX (
    'Sales',
    'Sales'[Sales] *
        LOOKUPVALUE (
            'Multiplier'[Multiplier],
            'Multiplier'[Customer Number], 'Sales'[Customer Number],
            'Multiplier'[Category], LOOKUPVALUE('Categories'[Categories], 'Categories'[Name], 'Sales'[Category Name])
        )
)

    4.Drag the measures into the table visual. The result is shown below.

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    2NV_DB , best it to merge these tables in Power query to get Multiplier value into the merged table and then you ca create a simple column

    Merge Tables (Power Query) : https://youtu.be/zNrmbagO0Oo

     

    = [Sales]* [Multipler]

     

    Otherwise in dax , first add a column to get Cargories into the sales, then another column to get Multipler

    refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
    https://www.youtube.com/watch?v=Wu1mWxR23jU
    https://www.youtube.com/watch?v=czNHt7UXIe8

     

     

    • 2NV_DB's avatar
      2NV_DB
      Frequent Visitor
      2 years ago

      So in this case I would need to merge the Categories into the Sales tablefirst, and then this resulting table into the Multiplier table?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi 2NV_DB ,

    Based on my testing, please try the following methods:

    1.Create the sample table.

    2.Create the relationships between three tables.

     

    3.Create the new measure to calculate sales values.

    Sales with Multiplier = 
SUMX (
    'Sales',
    'Sales'[Sales] *
        LOOKUPVALUE (
            'Multiplier'[Multiplier],
            'Multiplier'[Customer Number], 'Sales'[Customer Number],
            'Multiplier'[Category], LOOKUPVALUE('Categories'[Categories], 'Categories'[Name], 'Sales'[Category Name])
        )
)

    4.Drag the measures into the table visual. The result is shown below.

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.