Ranking issue ... Please help

[Sameer94]

Hi,

I have two tables with me. One is table 1, which has a column "Market" in it, and another table is table 2, which has "Customer" in it. Table 1 is linked with table 2 via Customer ID (Many to many relationship). Now, I have created a measure that is calculating sum of sales (Column sales present in table 1). Now what I need is, I have to create a table (visual) with 4 columns : First one being the "Market" (coming from table 1), second one being "Rank of customer" in that market based on total sales, third being the "Customer", forth being "Total Sales". I have attached an excel screenshot for what I need:

I want help creating ranking measure to achieve the same. Please note, I dont want to create a table in backend as the measure total sales will be dynamic ( to calculate current month, ytd, mat values), hence I want this to be in a table visual.

Please help, I m stuck for a day now, and now looking for experts.

Thanks a lot, hope I was as clear as possible.

[harshnathani]

Hi @Sameer94 ,

Create 2 measures

Sum of Value = CALCULATE(Sum(Sheet1[Value]), ALLEXCEPT(Sheet1,Sheet1[Customer_ID]))

Ranking = RANKX(FILTER(ALL(Sheet1[Market],Sheet1[Customer_ID]), Sheet1[Market] = MAX(Sheet1[Market])),[Sum of Value])

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Sameer94]

Hi @harshnathani ,

The solution you mentioned was so close that it literally got me excited, however the catch is if I bring Customer Name into the table visual created, the system breaks, the ranking breaks. Can you get the code in such a way that I can get the corresponding customer name next to the customer ID or only just the customer name, and the ranking still works ?

Thanks a lot sir.

[harshnathani · Accepted solution]

Hi @Sameer94 ,

You Sheet 2 has customer id C_2 for multiple customers. Pls check that,

You will need to create a Calculated Colum for Lookup of Customer from Sheet 2.

Customer ID From sheet2 =

VAR SearchValue = Sheet1[Customer_ID]

RETURN

CALCULATE (

SELECTEDVALUE ( Sheet2[Customer Name], "a" ),

FILTER (

ALLNOBLANKROW ( Sheet2[Customer_ID] ),

Sheet2[Customer_ID] == SearchValue

),

ALL ( Sheet2 )

)

then create 2 Measures

Sum of Value = CALCULATE(Sum(Sheet1[Value]), ALLEXCEPT(Sheet1,Sheet1[Customer ID From sheet2]))

Ranking = IF(NOT(ISBLANK([Sum of Value])),RANKX(FILTER(ALL(Sheet1[Customer_ID],Sheet1[Market],Sheet1[Customer ID From sheet2]), Sheet1[Market] = MAX(Sheet1[Market]) ),[Sum of Value]))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Sameer94]

Hi @harshnathani 

This solution worked perfectly sir .... Thanks a lot 🙂

Also thanks for everyone else for replying 🙂

[harshnathani]

@Sameer94 ,

Happy to help.

Regards,

Harsh Nathani

[ryan_mayu]

@Sameer94 

you can create two measures

total = CALCULATE(sum(tbl1[Sales]),ALLEXCEPT(tbl1,tbl1[market],tbl1[customer]))

ranking = 
RANKX(ALLEXCEPT(tbl1,tbl1[market]),[total],,DESC,Dense)

However, I am stuck on the sorting. I think you want to group by market and sort by ranking.

Let's see if someone else can help you on that.

[Sameer94]

I am trying to upload a pbix file but cant find any option to upload ... The thing is as you did ... the Customer column is in same table ... for me its in different table (table 2) and as I dont want to do Lookupvalue () coz the original pbix file is already 3gb. Can you let me know how to upload a sample pbix file here ?

Please follow the link to get to the sample pbix file (and do not bring market and customer columns from different tables into one table through query editor or via Data section, they both will be together in table/matrix visual only ) :

https://drive.google.com/file/d/19Ihh00kzK-zYbqH68soDNsQp3L0yajmT/view?usp=sharing

Thanks for replying

[amitchandak]

@Sameer94 , refer sub-category rank in measure rank link

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
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-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[harshnathani]

Hi @Sameer94 ,

Please share smaple data of both tables.

Regards,

HN