Power BI - DAX language RANKX function for TOP 5 only

[snph1777]

I have a Power BI table report as below.

Products (dimension column)	ProductCount (measure)	ProductRank (measure)
Fridge	36	1
AC	30	2
Fan	28	3
Light	25	4
Chair	20	5
Bike	12	6
Car	5	7

I have developed this table report using a dimension column called Products, and two measures

called ProductCount and ProductRank.

The measures use COUNT and RANKX functions at the backend.

The ProductRank measure value is obvioulsy based on the ProductCount measure.

I am currently showing all the 7 different products.

Out client wants to show only the TOP 5 products, and show all other products as Rank 6, with the Product name 'Others'.

Products (dimension column)	ProductCount (measure)	ProductRank (measure)
Fridge	36	1
AC	30	2
Fan	28	3
Light	25	4
Chair	20	5
Others	17	6

There are some limitations I have in using Power BI. I am using the Power BI Service embedded in our client application, where I cannot create Calculated Columns or new TABLEs (with ADDCOLUMNS), unless I raise a ticket.  I can use Measure always.

Any suggestion you can provide ?

I am okay with any suggestion; it would be great if you can provide me a solution that uses only Measures. If not possible, I can take the option to create TABLEs or Calculated Columns.

[mahoneypat]

@jdbuchanan71  is right that you need to have Other in a column to do this right.  For fun, I came up with a way to hack itwith the measure below to get the result shown.  It is not exactly what you were looking for, but does convey the same info (but it does hijack the total value).

Top 5 and Other =
VAR top5products =
    TOPN (
        5,
        ALLSELECTED ( ProductCount[Products (dimension column)] ),
        [Product Count], DESC
    )
VAR top5sum =
    CALCULATE ( [Product Count], KEEPFILTERS ( top5products ) )
VAR others =
    CALCULATE (
        [Product Count],
        EXCEPT (
            ALLSELECTED ( ProductCount[Products (dimension column)] ),
            top5products
        )
    )
RETURN
    IF (
        HASONEVALUE ( ProductCount[Products (dimension column)] ),
        top5sum,
        "Others - " & others
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[snph1777]

Hello  @mahoneypat 

I am using this in a visual, so really don't need a total;  I am planning to show this in a pie chart.

Can i do this, showing the 'Others' as a 6th rank ?  Will I get 17 for Others?

[mahoneypat]

This approach wouldn't work in a pie chart.  You will have to do one of the suggestions before to get an "Other" value in your products column.

Regards,

Pat

[snph1777]

Thanks for the clarification. Will keep you posted.

[snph1777]

@jdbuchanan71

Our client has finally agreed to create a calculated tablle.

Will update you in the next day or so.

Thanks very much. Appreciate your help so far.

Will keep you posted.

[jdbuchanan71]

@snph1777 

I don't know of any way to do it with only measures.  The reason being, you need somwhere that the value of 'Other' exists along with the list of products.  That is a simple calculated table to create with code like this:

Product List = 
UNION ( 
    DISTINCT ( 'Product'[Product Name] ),
    ROW("Product Name", "Other")
)

You join this new table into your existing product table and pull the product name from the new table for your report.

Then you can make a TopN measure that will show the top 5 and group the rest.

TopN = 
VAR Top_N = 
    CALCULATETABLE ( 'Product List', TOPN ( 5, ALLSELECTED ( 'Product List' ), [Sales Amount] ) )
RETURN
    IF ( 
        NOT ISFILTERED ( 'Product List'[Product Name] ), CALCULATE ( [Sales Amount], ALLSELECTED ( 'Product List' ) ),
        IF ( SELECTEDVALUE ( 'Product List'[Product Name] ) = "Other",
            CALCULATE ( [Sales Amount], EXCEPT ( ALLSELECTED ( 'Product List' ), Top_N ) ),
            CALCULATE ( [Sales Amount], INTERSECT ( 'Product List', Top_N ) )
        )
    )

In order to sort 'Other to the bottom of the list you would need a measure liks this and add it into your visual then set the column width narrow enough to hide it.  It is just there to sort.

TopN Sort = 
IF ( SELECTEDVALUE ( 'Product List'[Product Name] ) = "Other", 0, [TopN] )

[snph1777]

Thanks @jdbuchanan71

Will get back.

Are there any alternate solutions too ?