Measure for data over multiple tables with multiple dimensions

[2NV_DB]

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

[Anonymous · Accepted solution]

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]

@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]

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