Calculated members [measure * dimension]

[samloud]

Hi there,

Newbie here - tip of the hat to y'all.

Situation:

Sales Cube. We sell tablets in many pack sizes. We have PackSize as a dimension, so we can see how many 1 packs, 6 packs, 12 packs etc that we sell.

This is expressed in a PackSales measure, which shows how many packs were sold.

We now wish to add another measure which shows total tablets sold.
Aha, I think, calculated member! I've done this before, but only using measure * measure, or measure / measure. Never where it's dimension * measure, or measure / dimension.
I guess it's unusual for a dimension to be numeric.
So, really I'm looking for a calculate member where, theoretically:
([Measures].[Sales Quantity]*[dimension].[Pack Size])

How is this possible?

 

[Hurdles]

You cant multiply dimension by measure. Dont think of 1 pack, 6 pack and 12 pack as numbers. They are just members of a dimension, like beer, cola, diet.

At first glance maybe the easiest thing would be to put an IF stmt inside your calc member and hard code your multiplier.

IIF([PackSize].currentmember = [6 Pack],
[Sales Quantity] * 6,
IIF([PackSize].currentmember = [12 Pack],
[Sales Quantity] * 12,
[Sales Quantity]))

 

[samloud]

Thanks Hurdles

Oh dear. I was counting on being able to do that with the equivalent of a CAST sort of thing. But your beer/diet/cola point makes perfect sense.

I could do the IF statement thing, but there's a lot of different pack sizes, and the list of pack sizes could grow.

Hard coding multipliers always comes back to bite me! But I did ask for a quick / dirty solution, so thanks.

Anybody else got any ideas?

 

[MDXer]

Hurdles approach will work but sometimes nested logic can be difficult to implement and slow on the process speed. A more dynamic approach would be:

Add a Member property to your Packet Size dimension and populate it with the tablet count for each packet 6 or 12. You can call member properties in your calculated measures so you could do

[Measures].[Sales Quantity] * [Packet Size].CurrentMember.properties("Packet Volume")

("Packet Volume&quot would be the name of your member property.

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

 

[samloud]

That sounds better.

I've given it a try, and that formula keeps failing with syntax errors.

After thinking about it, I think I know why it's failing. There isn't a measure that goes down to individual product level.

(I didn't design this thing, I hasten to add)

So that formula would have been trying to multiply the pack quantity measure by an aggregated pack size.

or something....

So, I've fallen back on the 'replace fact table with a fact view) technique, and it's working really well.

Thanks for your help guys.

Any recomendations for good OLAP books?