Joe is an Avid Reader. No contributions for 2024.
Without a doubt, there will be something to
share on the Community in 2025!
User bio
404 bio not found
Member since Aug 17, 2015
Replies:

Here is another example of getting the top N and showing the percentage of the total using the HoleFoods cube in SAMPLES.

To get the top N with Analyzer, you need to both sort the values in descending order and specify the value for N.

First I added Outlet.H1.Country to the Rows box,

Then I clicked the gear icon to the right of "Country" in Rows box, which invokes the Level Options dialog.

The highlighted red box shows the sorting by descending Revenue and returning the first N members.

These settings combined will show you the top 10 countries by Revenue.

There are several ways to get the percentage of the total with a calculated member (measure).

Evgeny's answer is one way.

Here is different expression that uses %ALL:

Measures.[Amount Sold]/(Measures.[Amount Sold],[Outlet].[All Outlet].%ALL)

Another expression that uses %MDX to get the same result:

Measures.[Amount Sold]/%MDX("select Measures.[Amount Sold]on 1 from HOLEFOODS","%CONTEXT","filters|columns")

Here is the calculated member dialog in Analyzer using the first expression.

Here is the result:

In 2016.1, we added several methods to %DeepSee.Utils to get and execute MDX from a pivot table:

  •  %GetMDXFromPivot(): returns MDX text for a pivot table. Optionally executes the query.
  •  %ExecutePivot(): executes the MDX for a pivot table. Optionally returns the associated %DeepSee.ResultSet.
  •  %GetResultSetFromPivot(): returns the %DeepSee.ResultSet for a pivot table.  Optionally executes the query.

For details, see the class reference for %DeepSee.Utils.

Certifications & Credly badges:
Joe has no Certifications & Credly badges yet.
Global Masters badges:
Joe has no Global Masters badges yet.
Followers:
Joe has no followers yet.
Following:
Joe has not followed anybody yet.