Writing a measure between 4 different tables

[tjarnigan]

I am relatively new to Power BI and the DAX language. Here is my issue:

Table (relevant columns to form relationships):

Cube (BoxSize (Varchar), Box Size Numeric (Decimal)

Inventory (BoxSize (Varchar), Inventory Identifier)

Sales Order (Sales Order Number)

Sales Order Transaction (Sales Order Number, Inventory Identifier, Shipped Qty)

I want to know the numeric size of every sales order, in either the Sales Order Table and/or the Sales Order Transaction tables. So essentially I am looking to write a measure that is:

'Cube'[box size numeric] * 'Sales Order Transaction'[Shipped Qty] for each individual Sales Order

Does anyone have advice for how to do this? I have added the 'Cube'[Box Size Numeric (Decimal] to the Sales Order Transaction table using the LOOKUP function, but I'm not sure that this helps. 

[MattAllington · Accepted solution]

sorry about before.    I had to run and didn't want to give you another wrong answer, so I needed to test.  This should work

=sumx(size,CALCULATE(sum(Orders[Qty])) * size[Size])

edit:  Sorry, this next one is better  and what I had in mind originally

=sumx(Orders,Orders[Qty] * RELATED(size[Size]))

Just rename the columns you need

Best to avoid calc columns if you can. It's not wrong, just better http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

[v-shex-msft · Accepted solution]

Hi @tjarnigan,

Does MattAllington's solution help with your requirement?

Regards,

Xiaoxin Sheng

[MattAllington]

Read my article about data shape here http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

you should reshape your tables before loading. It looks like a Sales Order is superfluous, so don't load it. I can't tell if you should join Cube and inventory into a single table, but it looks like maybe.  You need a single lookup (dimension) table that contains all the unique inventory items and their cube. Join this (1:many) to the Sales Order Trans table and rename it to make it easier.  

Then you can write this measure 

total cube = sumx(values(transactions[order number]),transaction[qty] * related(cube[size]))

[tjarnigan]

Hi Matt,

Thank you for your prompt reply.

I have done as you suggested and created a separate lookup table that contains the Inventory # and their cube.

However, the formula you provided is not working. It does not allow me to use transaction[qty] * related(cube[size]) and I'm not really sure what the issue is. The error message that pops up under the DAX script says "A single value for column 'Order Qty (ncustord)' in table 'Sales Order Transaction' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max..."

[MattAllington]

ahhh, my bad.  Sorry I am about to run into a meeting.  I will come back to this

[tjarnigan]

I calculated the Cube for each line item in the Sales Order by creating a Calculated Column = Order Qty * Cube Size. So now if I want to create a measure that adds up the total cube for each Sales Order, how would I script that?

[MattAllington · Accepted solution]

sorry about before.    I had to run and didn't want to give you another wrong answer, so I needed to test.  This should work

=sumx(size,CALCULATE(sum(Orders[Qty])) * size[Size])

edit:  Sorry, this next one is better  and what I had in mind originally

=sumx(Orders,Orders[Qty] * RELATED(size[Size]))

Just rename the columns you need

Best to avoid calc columns if you can. It's not wrong, just better http://exceleratorbi.com.au/calculated-columns-vs-measures-dax/