# [Mondrian] FW: Mondrian issues with date and fact table columns

Julian Hyde julianhyde at speakeasy.net
Tue Jul 10 12:28:47 EDT 2007

```

For the 1st issue, I have a more specific problem. An order has parts. The
fact table has a record for each part in an order with the part_qty and unit
price.

I have a problem in creating a calculated mesaure for the sale amount i.e
part_qty * unit price. I want to get the sale amount for each order or for
each part in order.

The basic schema design is like this

Dimensions

Order

Part

Fact Table

Qty, unit price

Measures

nQty  : sum of Qty

nUnitPrice : sum of Unit Price

Calculated Measure

Total Sale : Measures.nQty * Measures.nUnitPrice

Total Sale on Part : ([Part].CurrentMember , [Measures].[nQuantity])
* ([Part].Currentmember, [Measures].[nUnitPrice])

I created two calculated measures as above. None of them gives the correct
order sale amount. They add all qty of all parts in an order and add all
unit price for all parts in order and multiply the two which is incorrect.
But if I use part dimension on one axis, order dimesion on another axis and
calculated measure on slicer axis I get correct part sale amount but the
order amount is still as calculated above.

Eg

Order No.    Part Num       Qty       Unit Price

1                 905                1                75

1                  50                 1              150

This order sale amount should be 225 but the calculated measure gives 450.

Questions:

1.	Is it somehow possible to show that within each Order there are
parts in the Calculated measure definition so that group by is done on order
no, part num.

2.	How is it possible to show that part is alevel within order  where
order and part are two different dimensions?

Please clarify these issues and help to create a calculated measure for the
sale amount of an order.

You need to introduce a new SQL expression price = quantity * unit_price,
and create a measure on it, and make that measure additive.

You can do this using the MeasureExpression element or by creating a view.

Julian
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20070710/3fd85747/attachment.html
```