question

genuiForex avatar image
genuiForex asked

Calculating Average Daily Quantity

Hi, Im a newbie in MDX and was challenged in this scenario: I'd like to calculate the average daily quantity (adq) for each product. Currently i have this calculation in my Cube /* Calculations to get Average Quantity Sales */ CREATE MEMBER CURRENTCUBE.[Measures].[Average Quantity] AS [Measures].[Quantity]/[Measures].[Registered Store Count], FORMAT_STRING = "Currency", NON_EMPTY_BEHAVIOR = { [Quantity], [Registered Sales], [Registered Sales Date] }, VISIBLE = 1 ; Some of the product daily quantity average are distorted as '[Measures].[Registered Store Count]' aren't reflected correctly because not all product are being offered to all store. Question, how to I factor in if a product already exist in a store or not whereas 1. If product exist in a store with quantity sold, count as part of the average calculation 2. If product exist in a store but no registered quantity sold (0 Qty Sold), count as part of the average calculation 3. If product does not exist (do not offer) in a store, exclude as part of the average calculation Do i need to add dimension to hold the information pertaining to availability of the product per store? Here's my cube diagram ![SSAS Cube Schema][1] [1]: /storage/temp/4627-cubediag.jpg Thank you for the help!
ssasmdxcube
cubediag.jpg (52.3 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.