Question
Ryan Miller · Oct 12, 2016

How to do a TOP 15 Payers and Percent of Total Paid

I'm stuck on how to show the top 15 payers and then also show the percentage of the total amount paid for each one.  The total amount is what was paid by all the payers not just the top 15. 

0
0 259
Discussion (7)1
Log in or sign up to continue

In DeepSee Analyzer in one Pivot Table. 

I came up with this using the SAMPLES database:

SELECT Description,  TicketsSold, Total, (TicketsSold/Total) as Percentage FROM 

(

SELECT TOP 15 Description, TicketsSold, SUM(TicketsSold) as Total  

FROM Cinema.Film

ORDER BY TicketsSold DESC

)

I'm not sure I'm following. Is that a MDX Query? It doesn't quite seem to be following MDX.  I don't see where you're specifying rows and columns.  The functions don't seem to have the correct syntax as well. 

In my rows I want to have TOP 15 Payer names  sorted descending by Payment Amount.  In my Columns I want to have Payment Amount, and Percent of Total (which shows what percent each of the top 15 payers have paid of the total amount) The total amount is not only from the top 15 payers, it would be from many more than the top 15. 

Hi, Ryan!

Let me show you this via Samples:

This MDX will do the job:

SELECT NON EMPTY {[Measures].[Amount Sold],[MEASURES].[PCT REVENUE SOLD]} ON 0,NON EMPTY HEAD(ORDER([Product].[P1].[Product Category].Members,Measures.[Amount Sold],BDESC),5) ON 1 FROM [HOLEFOODS]

Where PCT REVENUE SOLD has the following expression:

Aggregate(Product.CurrentMember,Measures.[Amount Sold])/Aggregate(Product.CurrentMember.Parent,Measures.[Amount Sold])

This results in Analyzer with the following pivot:

It shows top 5 Product categories by revenue with percent to Total.

Hope this helps

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.