topic RANKX new column to get top 10 customers with Lowest % in DAX Commands and Tips

RANKX new column to get top 10 customers with Lowest %

m_roussakis — Tue, 12 Oct 2021 20:06:15 GMT

I tried using TOP N to filter a visual I have in order to get the top 5 lowest % values per customer. For some reason Top N Bottom is not working. Sometimes I get more than 5, sometimes less.

I read that a new column can be created using DAX RANKX feature in order to accomplish this, but having trouble getting it to work.

Here's what I'd like to do:

In Table 'XXX - Local' I want to rank the % values from 'XXX - Local'[% AAN SENT] from worst to best, and then diplay the top 10 worst in matrix table.

Thanks!

Re: RANKX new column to get top 10 customers with Lowest %

Jihwan_Kim — Wed, 13 Oct 2021 03:00:10 GMT

Hi,

I do not know how your data model looks like, but check the below picture and the attached pbix file.

Top 10 Worst : =
VAR _topN = 10
VAR _newtable =
TOPN ( _topN, ALL ( 'XXX-Local'[Category] ), [Percent AAN SENT :], ASC )
RETURN
CALCULATE ( [Percent AAN SENT :], KEEPFILTERS ( _newtable ) )

Re: RANKX new column to get top 10 customers with Lowest %

m_roussakis — Wed, 13 Oct 2021 17:30:53 GMT

This works great, thanks.

One more question: What if in my matrix table I wanted to also show the Top 10 for the next expanded level?

So using your example, lets say 'Ca013' has 10 further sub categories, each with their own %.

I would like to also be able to perform the top 10 function on the next subcategories once the category is expanded in matrix table.

Re: RANKX new column to get top 10 customers with Lowest %

Jihwan_Kim — Wed, 13 Oct 2021 17:34:59 GMT

Hi, can you share your sample pbix file together with describing how you want to see your expected result?

My sample does not have a sub-category, and sorry that I quite do not understand your question.

Thanks.

Re: RANKX new column to get top 10 customers with Lowest %

m_roussakis — Wed, 13 Oct 2021 17:43:40 GMT

Unable to share PBIX...sensitive info. But I can show you what I mean. Example: Category NORTH shows in the Top 5 @ 87.9%:

If I want to expand NORTH to see subcategories, I also want the same TOP 5 logic to apply to these records:

As you can see it doesn't.

Is this possible?

Re: RANKX new column to get top 10 customers with Lowest %

Jihwan_Kim — Wed, 13 Oct 2021 18:30:09 GMT

Value AVG top 3 : =
VAR topNnumber = 3
VAR topNtablecategory =
TOPN ( topNnumber, ALL ( Data[Category] ), [Value AVG :], DESC )
VAR currentcategory =
MAX ( Data[Category] )
VAR topNtablesubcategory =
TOPN (
topNnumber,
FILTER ( ALL ( Data ), Data[Category] = currentcategory ),
[Value AVG :], DESC
)
VAR categorylevel =
IF (
ISINSCOPE ( Data[Category] ),
CALCULATE ( [Value AVG :], KEEPFILTERS ( topNtablecategory ) )
)
RETURN
IF (
ISINSCOPE ( Data[SubCategory] ),
CALCULATE ( [Value AVG :], KEEPFILTERS ( topNtablesubcategory ) ),
IF (
ISINSCOPE ( Data[Category] ),
CALCULATE ( [Value AVG :], KEEPFILTERS ( topNtablecategory ) )
)
)