Using a new dimension to calculate the most frequent price
In this recipe, we're going to analyze the products and their prices. The requirement is to find the most frequent price, or Mode Price, in any context.
Finding the price for a particular product doesn't look like a problem. We can either read it in its properties if the price is available as a product member property, or we can calculate the average price based on the sales amount and the quantity sold.
Member properties are static and therefore choosing the first approach would be wrong. For example, the price in the form of the member property doesn't change in time or with the territory. What we need is a dynamic expression.
The other option is to calculate the average price, but that's also not good enough. We need to know the exact price for each transaction, not the average value.
If we go low enough with the granularity of the query, the average price will eventually become the price used in the transaction; however, cubes are...
