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 ...

Get MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.