Mar 25 2020 04:50 AM
Hi all, Is this the right place to ask for help? I was thinking i'd joined a forum where I can ask subject matter experts advice?
I need help writing an MDX query to sum two columns of data. Here's the background:
I've set up 2 x data tables
table 1 is inventory available stock. very simple : list of part numbers and how many available.
table 2 is a list of list works order detailing how many of each of the items is required. The complication here is that there's multiple rows as each works order requires multiple of items of inventory in order to manufacture it so it's displayed as multiple rows
I've set up a relationship between the tables
I've created a 1 to many relationship linked by part number
I've created a pivot table which brings in the data from both the above tables
I've brought in the part number from the available stock table, as the ROW
I've put a formula "SUM OF AVAILABLE STOCK "(the QTY available column from the available stock table) into the values section
I've put a formula "SUM OF QTY REQUIRED" (from table 2 which has multiple rows) into the values section as well
What I can see in my simple pivot is:
.Part number. .How many in stock. .How many I need in order to make my various items.
This is expressed as a positive number for available stock and then a negative number for QTY required
WHAT I WANT TO SEE:
I want to create a new calculation column at the end of my pivot table so I can see how many I actually need. What I think is that I need to create a new SET which performs a simple calculation:
QTY IN STOCK (ADD) QTY REQUIRED.
All in all, my pivot table would then have 4 headings:
part number
QTY in stock
QTY required in total
QTY required to complete works order (my new calculated field)
example
Part number : Widget 1
QTY in Stock : 10
QTY Required in Total : -20
QTY required to complete works orders : 10
I have figured out how to add a new set but then I end up needing to write a MDX query which is what I'm not able to do because I've never used it before so I'm completely green when it comes to the syntax and expressions.
Please can someone help? In my mind this should be REALLY simple because it's such a simple sum.