SQL Analysis Server Aggregation Calculation Problem

[mikelblanco]

Hi

I have a calculation problem in my cubes:

My goal is to calculate the cost of each individual sale by multiplying the quantity times the cost, and then to have the sum of these individual calculations to have a sum of all costs.

This is the setting:

I have a virtual cube (V) taking two different measures (Unitarycost and Quantity) from two different cubes (Custinvoicetrans and Inventtable). I have a calculated member called TotalCost which multiplies Quantity times Unitarycost for each article sold. I use dimensions Date and ArticleNumber to look at the data.

For example, I sort by article # 321 and date 6th of May: It says 2 units were sold and the UnitaryCost is $5. The calculated member multiplies Quantity times Unitarycost (5$) and therefore Totalcost is 10$. All that is correct if you look each article line by line. The problem is that when looking at totals (not sorting neither by date nor article), the calculated member Totalcost multiplies the aggregated cost measure (the sum of all present cost figures) times the aggregated Unit measure (the sum of all units).

This is not correct because to get this numbers what its doing is just multiplying the aggregated Quantity times the aggregated cost.

To get a real aggregated total for the costs it should just sum each Totalcost calculation. How can I do this?

 

[MDXer]

So you do not carry the cost as a value in the same record that quatity is provided by. If you have control this would be the easiest way. You could create a view that would combined the records used in the 2 cubes so that for each sale you also had cost then the current calc would work because it would some up your costs by each transaction.

Having the value carried in 2 seperate cubes and then calculated in a virtual cube is going to require you to use some calculation in a calculated cell. You would apply the Subcube to your measure (calculated measures can't be used) with the criteria being:

IsLeaf(Product.CurrentMember) = 0

The above criteria will evaluate to 0 at any level except you leaf level of your products dimension. You will then need to use a calculation that will sum up the unit sold * unit cost for each individual member of the leaf level.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[mikelblanco]

Hi MDXer

First of all, thanks for your reply, I was getting desperate. I'm not sure I f I understand your words but it seems that I'm doing it the hard way. I can rebuild the cubes, that would not be a problem. Which way you think would be the easiest one? What do you mean by "having control"? Where do I execute the code to create he view?

Thanks man