Ranking ignoring Market(category)

[youconnect]

Hi,

I have this DAX 

RANKX(ALL(users_table;CALCULATE(SUM(sales_table_sales);;DESC)

And my output is:

idUser	Sales	Rank
56	10000	1
55	9000	2
214	5000	3
59	4500	4
874	2600	5
68	2000	6
965	1900	7
57	1800	8
63	1500	9
54	1000	10

But if I add the Market to the visual table the result is this:

idUser	Sales	Market	Rank	Rank Expected
56	10000	alpha	1	1
55	9000	beta	1	2
214	5000	zeta	1	3
59	4500	zeta	2	4
874	2600	beta	2	5
68	2000	alpha	2	6
965	1900	alpha	3	7
57	1800	beta	3	8
63	1500	zeta	3	9
54	1000	alpha	4	10

Can you help me?
Thanks in advance.

[Anonymous]

Create two measures like:

sum sales = SUM('Table'[Sales])

rank= RANKX(all('Table'),[sum sales],,DESC)

'rank' measure will give the desired output.

[v-lionel-msft]

Hi @youconnect ,

Could you give me a sample data model?

I need to know which columns do users_ table and sales_table have?and what's the relationship between the two tables?

Best Regards,
Lionel Chen

[youconnect]

@v-lionel-msft here you have pbix file

https://we.tl/t-qUSAEUvTcg

Help me please

[amitchandak]

The formula you provided, seems to have a issue.

I think it should be created as a column not measure.

Refer

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[youconnect]

@amitchandak 

Thanks for your respose.
I already saw the articles but without sucess.

Anybody can help me?

Thanks

[amitchandak]

@youconnect 

Please check at : https://www.dropbox.com/s/vy0t74qwkuxhogf/rank_test.pbix?dl=0

refer: New Rank 1, New Rank 2