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 sal...
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.

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?