Rank sales based on categories and sub-categories

[johnyip]

The table has 3 columns, [Company], [market], [sales], and the data is like the following:

Company	market	sales
A	LOCAL	1000
B	LOCAL	2000
C	LOCAL	3000
D	EUROPE	4000
E	EUROPE	5000
F	EUROPE	6000
G	ASIA	1000
H	ASIA	2000
I	ASIA	3000

I want to rank the sales using market as the parent, and the company as the child, with the following expected result:

Company	Market	Sales	Ranking
A	LOCAL	1000	3
B	LOCAL	2000	2
C	LOCAL	3000	1
D	EUROPE	4000	3
E	EUROPE	5000	2
F	EUROPE	6000	1
G	ASIA	1000	3
H	ASIA	2000	2
I	ASIA	3000	1

I tried many DAX on my own and cannot come up with an answer to achieve that. I would be appreicated if anyone of one can assist me.

[amitchandak · Accepted solution]

@johnyip , Try a new column rank

Rank = rankx(filter(Table, [Market] = earlier([Market])), [Sales])

a measure rank

Rank = rankx(filter(all(Table[Market], Table[Comapany]), [Market] = max([Market])),calculate(Sum( [Sales])))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Jihwan_Kim · Accepted solution]

Hi,

One of ways to solve this is to wrtie the measure like below.

Please check the below picture and the attached pbix file.

Sales Measure: =

SUM( Data[sales] )

Ranking measure: =

VAR currentmarket =

MAX ( Data[market] )

VAR currentsales = [Sales Measure:]

VAR newtable_filter =

FILTER (

ALL ( Data ),

Data[market] = currentmarket

&& [Sales Measure:] >= currentsales

)

RETURN

COUNTROWS ( newtable_filter )

[johnyip]

Thank you for the answer, this works well too 🙂

[amitchandak · Accepted solution]

@johnyip , Try a new column rank

Rank = rankx(filter(Table, [Market] = earlier([Market])), [Sales])

a measure rank

Rank = rankx(filter(all(Table[Market], Table[Comapany]), [Market] = max([Market])),calculate(Sum( [Sales])))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[johnyip]

Thanks for the answer, the output of my pbix file is just like what I expected. But it is unfortunate to say that I cannot share the sample data here, sorry for that.