Maximum value of a measure from an MDX result set

Below is an MDX Query which returns the sales of different product categories:

select {[Dim Category].[Category].[Category]} on 1,
{[Measures].[Measure Value]} on 0
from [MY CUBE]

result set returned:

We can see that the maximum value is 42092.83. Using max and axis function, we can show the maximum value as a seperate measure. So, we add a measure and the query becomes :

with
member [Measures].[MAXSales]
as
max(axis(1),[Measures].[Measure Value])

select {[Dim Category].[Category].[Category]} on 1,
{[Measures].[Measure Value],[Measures].[MAXSales]} on 0
from [MY CUBE]

the result set looks like this:

Advertisements

About Zahid

Team Lead at IMS Health (www.imshealth.com). A part-time consultant and trainer. Fields of expertise are ETL, BI Reporting (Microstrategy, Excel, ASP.NET Dashboards etc.) Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in MDX, SSAS (Analysis Service), Cube and tagged , , , . Bookmark the permalink.

2 Responses to Maximum value of a measure from an MDX result set

  1. Sonal Bajaj says:

    Hi, Thanks very much for this solution. But on using this code my measure value becomes absurd and does not remain same after adding MAX calculated emmber. Please help.

  2. Zahid says:

    It’s probably because of Axis. Check if you are referring the correct axis or not.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s