Forum Discussion
Ranking Measure Help over Peer Group
I have below scenario in my datamodel and looking for solution to create measure for ranking with vendor versus peer vendor.
Table A:
VendorID | Date | Qty |
A | 2021-01-31 | 100 |
B | 2021-02-01 | 50 |
C | 2021-02-05 | 150 |
D | 2021-02-10 | 100 |
Table B
VendorID | Peer_VendorID |
A | B |
B | C |
B | D |
C | A |
D | B |
Based on above two tables, trying to create measure for ranking for each month and it should show the peer vendor rank too?
Any suggestions/help over DAX or data model greatly appreciated.
10 Replies
Maybe something like this:
Rank = RANKX(ALLSELECTED(Table2[PeerVendor]),CALCULATE(sum(Table1[Qty]),ALLEXCEPT(Table2, Table2[PeerVendor])))When you display that in say a matrix with month along the columns you will find it shows the rank by month.I did create dynamic rank measure but looking for peer rank using another table in relation in dynamic way. the suggested solution works for regular rank only.
Hi samirineni,
Can you advise me what the expected output you need?I may have a misunderstanding...
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Hi samirineni ,
How to show the peer vendor rank,is it something like below:
If so,create 2 columns as below:
_rank 1 = RANKX(FILTER(ALL('Table A'),MONTH('Table A'[Date])=MONTH(EARLIER('Table A'[Date]))),'Table A'[Qty],,ASC)_rank 2 = LOOKUPVALUE('Table B'[Custom],'Table B'[Peer_VendorID],'Table A'[VendorID])And you will see:
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
I am not sure why you added index value as static and it doesn't help. i have derived my regular dynamic rank but looking help to create peer rank using another table with relationship.
Hi v-kelly-msft
I am looking for create selected vendor rank and correpsonding peer vendor rank. The peer vendor can be determine based on peer vendor table relation ship where vendor can be determined from vendortxn table. we have another table called vendor master. so if we select any vendor from vendor master, we need to get vendor rank from vendortxn table( it contains all vendor ID and totalqty over Yearmonth) and Vendor and their peer vendor can ne determined based on vendor_peer table which holds just vendor ID and Peer Vendor ID. Peer vendor transaction list in second tablke which is vendor transaction table.
Thanks,
Sreenivas
Hi samirineni ,
So what's in vendortxn table?Can you provide more details?
Based on Table A,I could get a result according to month qty as below:
VendorID
Date
Qty
rank
A
2021-01-31
100
1
B
2021-02-01
50
1
C
2021-02-05
150
3
D
2021-02-10
100
2
But with Table B,I dont know how to get a ranking result based on the peer relationship.
VendorID
Peer_VendorID
rank??
A
B
B
C
B
D
C
A
D
B
Or can you show me a direct ranking result based on the sample data?
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
@ v-kelly-msft
Vendor Master Table
VendorID VendorName
1 VendorA
2 VendorB
3 VendorC
4 VendorD
Vendor Txn Table
VendorID TxnDate Qty
1 2021-01-10 100
2 2021-01-15 150
3 2021-01-20 50
4 2021-01-25 25
1 2021-02-10 100
2 2021-02-15 125
3 2021-02-20 170
4 2021-02-25 75
Vendor_Peer mapping table
VendorID PeerVendorID
1 2
1 3
2 4
2 1
2 3
3 1
3 4
4 2
4 3
The above 3 tables are Vendor Master which holds vendor details, vendor txn table contains transactions over each date and vendor_peer mapping will give list of peer vendors. I am looking to derive dynamic rank for selected vendor and corresponding peer vendors.
Lets say if I select Vendor A and data range is Jan, it should show dynamic rank for VendorA as 2, but I need corresponding peer vendors list too which are Vendor B and Vendor C.
So that is our choice to display in table visual with single column with list of Vendors and corresponding ranks or create multiple columns to display the same.
I hope this will help or let me know?
