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/
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..."
Community Championahhh, 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/