Power BI,Qlikview, Qlik Sense,Python,PySpark, Tableau,Oracle, SQL Server,SAP BO, BODS, MSBI

Top N of records in Webi report

Requirement:

We have a list like the one below:
Dimension Measure
A 30
B 15
C 40
D 41
E 20

We need a ranking for the TOP 3 (D,C,A) and also we need to show a grouped row called OTHERS with the amount for the rest of Dimensions which are excluded from the top 3.
In this example above, we need the Table as Follows:

D 41
C 40
A 30
OTHERS 35

Work Around:

For example, I want to show top 20 Dimension based on Measure data in a webi report.

click on ranking button select top 20

Now click on second column that is Measure,click on on sum button.

Now it will show extra row with sum label.

change the sum label to “Others“.

create a variable with following formula.
Others=NoFilter(Sum([Measure]))-Sum(Measure]).
Drag this variable instead of sum value column.

This will give you sum of Measure for Others.

One response to “Top N of records in Webi report”

  1. Patty Avatar
    Patty

    is there a way to do a % of the “others” as a percent of the total? (IE – the ranked items + others would aggregate to 100%)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.