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

Question

Question

Ryan Miller · Oct 12, 2016

#Analyzer #InterSystems IRIS BI (DeepSee)

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.

Discussion (7)0

Log in or sign up to continue

Evgeny Shvarov · Oct 12, 2016

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

1 0

Peter Steiwer · Oct 14, 2016

This documentation page has some "recipes" for creating calculated measures. What you requested is slightly more advanced than what is described here, and the two provided solutions are complete and precise. But I believe this section of the documentation provides some good recipes for building a calculated measure that does exactly what you need.

2 0

score 0 · Answer 1 · 2016-10-12T14:07:34-04:00

Ryan Miller · Oct 12, 2016

In DeepSee Analyzer in one Pivot Table.

0 0

score 0 · Answer 2 · 2016-10-12T14:44:26-04:00

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

)

score 0 · Answer 3 · 2016-10-12T15:11:21-04:00

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.

score 1 · Answer 4 · 2016-10-12T20:31:57-04:00

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:

score 0 · Answer 5 · 2016-10-13T12:13:19-04:00

Ryan Miller · Oct 13, 2016

Thank you. That was all very helpful. Got it now.

0 0