show top N/others with subcategories

[Anonymous]

I'm creating a top 20 / others table.

my rank measure is:
rank vendas loja = RANKX(all(d_loja[nm_loja]),[Total Vendido])

when I open subcategorie the rank "update"...

I only can show 5 stores per page (because I need export to power point), so I will filter top 1-5 on first page, 6-10 on second...
That's the result after my filter "top2", every rank is on position 1...

Using Tableau we have the "fixed" function, can we use something on PowerBI?
I'm doing something wrong?

my dataset

https://www.dropbox.com/sh/fjqpjzi8w8jp8jq/AAAUyXIFU3ILfuCQpAOXYwUOa?dl=0

[v-yuezhe-msft]

@Anonymous,

Create a new table using DAX below.

Table = SUMMARIZE(f_vendas,d_loja[nm_loja],d_categoria[nm_categoria],"total",SUM(f_vendas[vl_quantidade]))

Then create a measure in the new table and check if it returns your expected result. If not, please post your desired result here.

Rank = RANKX(ALL('Table'[nm_categoria],'Table'[nm_loja]), Calculate(SUM('Table'[total]),ALLEXCEPT('Table','Table'[nm_categoria],'Table'[nm_loja])),,DESC,Skip)

Regards,
Lydia

[Anonymous]

you got very close!!

But that rank "ignore" the first group (nm_loja) and only rank the second (nm_categoria)

       

Can we create another measure who ranks only by nm_loja?

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Create another measure that ranks only by nm_loja using the DAX below.

Measure = RANKX(ALL('Table'[nm_loja]), Calculate(SUM('Table'[total]),ALLEXCEPT('Table','Table'[nm_loja])),,DESC,Skip)

Regards,
Lydia