June 22, 2011 at 12:30 pm
Hi,
Could you please help me with the MDX for calculated measure below?
When the user selects a shipment date and a truck, I have to calculate cost per Lb by dividing shipment rate by sum of net weight.
Customer Name Net Weight Shipment Rate
------------------- ------------- -----------------
A 20 2000
B 10
C 15
Grand Total 45 2000
In the above scenario, the cost per lb would be 2000/45 = 44.44. I then have to create another calculated measure by multiplying [cost per lb] by net weight of each customer to find the shipping cost for that customer.
The MDX for my calculated measure is
CREATE MEMBER CURRENTCUBE.[MEASURES].[Cost Per Lb]
AS ([Shipment Rates].[Customer Name].currentmember, (Sum (Existing [Shipment Rates].[Customer Name].Members, [Measures].[Rate])))/ (Sum (Existing [Shipment Rates].[Customer Name].Members, [Measures].[Net Wt])),
FORMAT_STRING = "#,#.00",
VISIBLE = 1 ;
I am getting an error message that reads 'The tuple function expects a tuple set expression for the argument; A string or numeric expression was used.'
Your help would be appreciated.
Best Regards, Uma
June 23, 2011 at 1:17 pm
Hello,
So I understand that you basically need to allocate values to members based on a value of a member at another level, the ALL member (the total shipment amount). Here is how I would approach the issue. I actually mocked this up in a development DB that I have access to so that I could see it working. Below is some MDX I have written based on some metrics and dimensions that I have. If it works for you, you'll need to modify the names to fit your environment.
The first task is to find the total cost per pound for all customers. Let's switch the terms on you just a bit to use members that I can demonstrate.
Instead of customers, I use the [Manufacturing Shift] dimension. A dimension is a dimension is a dimension. I could have picked any that have a usage relationship to my fact.
Pounds and cost have slightly different names but that should be obvious enough.
To find the total cost per pound of all shifts we need the total cost and the total pounds.
CREATE MEMBER CURRENTCUBE.[Measures].[Cost at All Cell]
AS (
[PE Received Standard Cost Amount],
[Manufacturing Shift].[Manufacturing Shift].[All]
),
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[Measures].[Pounds at All Cell]
AS (
[Measures].[PE Finished Good Receipt Pounds],
[Manufacturing Shift].[Manufacturing Shift].[All]
),
VISIBLE = 1;
The math here is easy and from your post, it looks like you have this part figured out.
CREATE MEMBER CURRENTCUBE.[Measures].[ALL CELL Cost Per Pound]
AS [Measures].[Cost at All Cell] / [Measures].[Pounds at All Cell]
,FORMAT_STRING = "#,#.000000", VISIBLE = 1;
Now, the interesting part...well, once you see it, it's actually quite simple so I'm not sure how "interesting" it will be. Now that I have the cost per pound for all shifts, I can use it as my multiplier.
CREATE MEMBER CURRENTCUBE.[Measures].[Cost Per Pound Allocated]
AS [Measures].[ALL CELL Cost Per Pound] * [Measures].[PE Finished Good Receipt Pounds]
,FORMAT_STRING = "#,#.00", VISIBLE = 1;
And that's it! Three simple calcs. I'm sure there is a way to make the calculation more generic so that it works with any dimension. But this should work for you based on what you have described. As it is, the calc is "fixed" to the Shift dimension...in your case it will be "fixed" to the customer dimension.
I have included a link to an image which shows this in action to prove that it works. In my case, I already have a cost at every member that I am evaluating, but that does not matter because I ignored it. To prove it I wrote a quick QA calc and tossed it into the browser. It looks like this:
CREATE MEMBER CURRENTCUBE.[Measures].[Cost Per Pound MEMBER QA]
AS [Measures].[PE Received Standard Cost Amount] / [Measures].[PE Finished Good Receipt Pounds]
,FORMAT_STRING = "#,#.00", VISIBLE = 1;
Oh and don't forget to allow yourself enough significant digits in your allocation calc to prevent rounding errors. Never forget the leasons learned from "Office Space".
I hope this helps.
Chris Umbaugh
Data Warehouse/BI Consultant
@chrisumbaugh twitter
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
June 23, 2011 at 1:34 pm
Hi Chris,
Thank you for the reply. I don't have the rate for each customer in the database. The rate applies to a particular shipment date by shipment truck as it carries more than one customer order.
Shipment DateShip Day Ship Agent Code
2011-06-05 00:00:00AllXXX
Customer Total Wt RateCost/Lb Shipping Cost
A5602,0903.73 2,090.00
B1,602.00.00
C450.00.00
D576.00.00
E820.00.00
F2,289.00.00
Grand Total6,2972,090.33 2,090.00
In this example above, my cost per lb would be 2090/6297 = 0.33. I then have to multiply this cost per lb by total weight for each customer.
Currently, my calculated measure reads as below.
CREATE MEMBER CURRENTCUBE.[MEASURES].[Cost Per Lb]
AS (sum([Measures].[Rate]) / sum([Measures].[Total Wt])),
FORMAT_STRING = "#,#.00",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[Shipping Cost]
AS sum([Measures].[Cost Per Lb]) * [Measures].[Total Wt],
FORMAT_STRING = "#,#.00",
VISIBLE = 1 ;
This is not working as it shows a different value for Cost Per Lb for customer A above and 0 for the rest of the customers in the list.
Would appreciate your help on how to fix this issue.
June 23, 2011 at 1:59 pm
Hi Chris,
I looked at the URL you posted along with the script. After going through the information a couple of times, I was able to make the changes accordingly in my script.
I have been researching online for the past two days on how to get this done.
Thank you very much for helping me! You really made my day!
Best Regards,
Uma
June 23, 2011 at 2:09 pm
Happy to help!
Chris Umbaugh
Data Warehouse/BI Consultant
@chrisumbaugh twitter
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
June 28, 2011 at 8:53 pm
Hi Chris,
I can browse the cube if I filter by any dimension except customer name.
CustomerNameRateRateAtAllCellsTotalWt
A 1010 12
B 10 10
C 10 11
As soon as I choose a customer name, the 'rate' measure and 'Rate at all Cells' show zero.
CustomerName Rate RateAtAllCells TotalWt
B 10
This customer name is an attribute (Keycolumn: CustomerNo ; NameColumn: CustomerName) in my dimension called Shipment (Key column: CustomerNo, Shipment Date, ShipAgentCode ; NameColumn: CustomerNo).
I checked my dimension usage tab and the relationship is set up correctly.
Is there any property that can be set up for this dimension so that it shows the rate when I filter by customer?
Regards,
Uma
June 29, 2011 at 11:02 am
Uma,
Sounds like you need to SCOPE the calculation. SCOPE over-rides a member when the specified set is "in scope". If I have some time tonight, I will shoot a couple of examples your way. Until then, read a bit on using SCOPE in MDX. It is powerful and flexible but could quickly get out of hand if not applied judiciously. One thing to keep in mind while you are reading: order of operation is important!
Chris
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
June 29, 2011 at 11:07 am
Thank you, Chris! I will research on this topic online to learn more.
Best Regards,
Uma
July 1, 2011 at 6:40 am
umas (6/28/2011)
This customer name is an attribute (Keycolumn: CustomerNo ; NameColumn: CustomerName) in my dimension called Shipment (Key column: CustomerNo, Shipment Date, ShipAgentCode ; NameColumn: CustomerNo).
Uma,
Have you made any progress on this? I apologize for my tardiness on responding.
I am unclear on what is happening in your environment. Perhaps there is a more basic structural problem. Tell me more about the "Shipment" dimension. What is the key attribute for the "Shipment" dimension? What attribute hierarchies do you have built? What are the attribute relationships for the "Shipment" dimension?
Are you trying to calculate the "cost per shipment" or the "shipment cost per customer"? What are the measures that are in play? Finally, can you display the calculations that you have currently in the order which they appear in the calculation script?
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
July 1, 2011 at 12:41 pm
Thank you, Chris for replying to my post.
I changed the design to make the calculations work. Since there was a single rate to be applied to a group of customers for whom the orders where shipped on a particular day, I wrote update statements to apply that same rate to customers if the shipment date and shippingagentcode matched.
That solved the issue.
Thank you again!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply