Re: Problems Defining Measure
- From: "Harry Leboeuf" <HarryLeboeuf@xxxxxxxxxxxxxxxxx>
- Date: Thu, 24 Jan 2008 11:10:56 +0100
Hi,
I've been playing with your suggestions, and mayby i've been makign some
mistakes but i'm a bit stucked ..
For example the calculation for the sum of the TUR's
SUM( { { [Complex].[Country Complex].CurrentMember }
* { [Group Calendar].[Calendar Time].CurrentMember }
* { [Asw ProductGroup].[ProductGroup].Members }
},
[Measures].[TUR])
With this i'm able to calculate the total of the tur's for every
Complex/Site in every month.
But the hierarchie of Complex is (All) - Country - ComplexId. when looking
at every Complex Id i'm getting values, but when i drill up to the Country
Level i have no values ...
"entaroadun" <johnny.c.kwan@xxxxxxxxx> wrote in message
news:52b693cc-848a-464b-b02e-cfbef564c5bb@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
OK. This is pretty easy once we restate what the base units for the
various measures really are.
But first, an administrative comment. Is the Month field actually a
month of year field, i.e. 1-12 instead of 200701-200712? If so, the
Month attribute will need to incorporate the Year field as part of its
key definition.
Take Up Rate is actually measured in Items/Visit.
Average Item Price is Sales/Item
You want:
Total Items Sold - measured in Items
Net Sales - measured in Sales
Average Take Up Rate - measured in Items/Visit - this is the same as
Take Up Rate at the Product Group level, but across Product Groups is
a consolidated rate
Sales per Visit - measured in Sales/Visit
1. Set the AggregationFunction for Take Up Rate and Average Item Price
to NONE.
2. [Total Items Sold] = SUM(([Site],[Month],[Visits])*([Site],[Month],
[Product Group],[Take Up Rate]))
3. [Net Sales] = SUM(([Site],[Month],[Visits])*([Site],[Month],
[Product Group],[Take Up Rate])*([Site],[Month],[Product Group],
[Average Item Price]))
4. [Average Take Up Rate] = [Total Items Sold]/[Visits] (I highly
recommend using a SCOPE statement to set [Average Take Up Rate] =
[Take Up Rate] at the lowest level.)
5. [Sales per Visit] = [Net Sales] / [Visits]
On Jan 21, 10:59 am, "Harry Leboeuf" <HarryLebo...@xxxxxxxxxxxxxxxxx>
wrote:
My First FactTable is called 'BudgetProducts'
Fields
Site,Year,Month,ProductGroup,TUR(TakeUpRate),AvgItmPrc(AverageItemPrice)
My Second FactTable is 'BudgetCustomers'
Fields Site,Year,Month,Visit(BudgettedClients)
Dimension Calendar
Fields Year,Month,Date
Dimension ProductGroup
Fields ProductGroup,Desc
Dimension Site
Field Site,Desc
Calculations (The Ones I Defined)
TotalItemsSold = BudgetCustomers.Visit * BudgetProducts.TUR (This
calculates
by Site,Year,Month,ProductGroup the Items to be Sold)
NetSales = TotalItemsSold * BudgetProducts.AvgItmPrc (this calculates by
Site,Year,Month,ProductGroup the expexted Net Sales)
ATR = TotalsItemsSold / BudgetCustomers.Visit (An TUR by Site,Year,Month
(productgroup) at the lowest level this value was even provided, but if we
don't browse on the product group so we get a 'average TUR, witch is nog
the
same as the average of the averages)
SPH = NetSales / BudgetCustomers.Visit (This should give us at higher
levels
a Average by Customer)
I hope this is a first start for the data.
"entaroadun" <johnny.c.k...@xxxxxxxxx> wrote in message
news:5a4d5ec9-9f41-4ce3-a7e5-1e3ab5bddfc0@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Your calculation order is wrong to get the results that you want.
Instead of playing with calculation order though (which is deprecated
for 2K5), you need the right MDX script to get the results you need.
Let's start at the beginning. What are the dimensions connected to
the fact table, what attributes are in these dimensions, and what
attributes are on the fact table?
On Jan 21, 10:02 am, "Harry Leboeuf" <HarryLebo...@xxxxxxxxxxxxxxxxx>
wrote:
Hello,
In my Cube i have a measure group that could contain 2 measures. All
values
are budgetted values
First is the 'Average TakeUp Rate', expressed in Items / ProductGroup
Second is the 'Average Item Value', expressed in Value / ProductGroup
With the budgetted number of clients i could caulculate the budgetted
'Sold
Items', 'Average TakeUp Rate' * 'Budgetted Clients'
With the Average Item Value i then can calculate the Budgetted Net Sales
as
'Sold Items' * 'Average Item Price'
Then you could (if needed at this level) again calculate the 'Average
Item
Price' by doing 'Net Sales' / 'Sold Items'
All these figures are by Product Group. If i want to calculate the
'Average
Item Price' global then i would need the function 'Net Sales' / 'Sold
Items'....
But what the system is doing is that he sums the 'Average TakeUp Rate'
and
multiplies this by the 'Budgetted Client' (this works fine as the first
value in on a by client base budget)
Then he multiplies this value by the Sum of the 'Average Item Prices'
and
there it goes wrong. I know that the Measure Average Item Price should
not
be aggregated.
But setting the 'Average Item Price' aggreagations function to none
results
in the other calculated members not being calculated at the higher
levels.
Briefly, what i think i would need is that at the lowest level the data
is
fetched from the dsv, but any higher value this measure should be
calculated
...
But, i don't have an idea on how to get this started.- Hide quoted
text -
- Show quoted text -
.
- References:
- Problems Defining Measure
- From: Harry Leboeuf
- Re: Problems Defining Measure
- From: entaroadun
- Re: Problems Defining Measure
- From: Harry Leboeuf
- Re: Problems Defining Measure
- From: entaroadun
- Problems Defining Measure
- Prev by Date: how to add a new MG on a fact table that is already used
- Next by Date: Re: Problems Defining Measure
- Previous by thread: Re: Problems Defining Measure
- Next by thread: Re: Problems Defining Measure
- Index(es):
Relevant Pages
|
