Forum Discussion
Power BI - DAX language RANKX function for TOP 5 only
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
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?
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
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.
