Forum Discussion
Writing a measure between 4 different tables
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/
Community ChampionRead 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]))
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
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
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/