Article Joe Gallant · Apr 8, 2016 1m read Global Summit 2016 - Analytics Models: Thinking Outside the Box
go to post Joe Gallant · Oct 12, 2016 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 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:
Another expression that uses %MDX to get the same result:
Here is the calculated member dialog in Analyzer using the first expression.