Forum Discussion
Ranking Measure Help over Peer Group
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,
Kelly
Did 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?
Hi samirineni ，
First create 2 dimension tables for month and vendorID.
Then create 3 measures as below:
_rank = RANKX(FILTER(ALL('Vendor Txn Table'),'Vendor Txn Table'[Month]=MAX('Vendor Txn Table'[Month])),CALCULATE(SUM('Vendor Txn Table'[Qty])),,DESC,Dense)Measure = var _id=CALCULATE(MAX('Vendor Master Table'[VendorID]),FILTER(ALL('Vendor Master Table'),'Vendor Master Table'[VendorName]=SELECTEDVALUE(slicer[VendorName]))) Return CALCULATE('Vendor Txn Table'[_rank],FILTER(ALL('Vendor Txn Table'),'Vendor Txn Table'[Month]=SELECTEDVALUE('calendar table'[Month])&&'Vendor Txn Table'[VendorID]=_id))_peer vendor ID = var _id=CALCULATE(MAX('Vendor Master Table'[VendorID]),FILTER(ALL('Vendor Master Table'),'Vendor Master Table'[VendorName]=SELECTEDVALUE(slicer[VendorName]))) var _tab=CALCULATETABLE(VALUES('Vendor_Peer mapping table'[PeerVendorID]),FILTER(ALL('Vendor_Peer mapping table'),'Vendor_Peer mapping table'[VendorID]=_id)) Return IF(MAX('Vendor_Peer mapping table'[PeerVendorID]) in _tab,1,BLANK())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 trying to use your PBIX file and cannot derive the desired rank using measures that given in PBIX? Can you help how to read or you can see below data i am trying to derive using sample set provided by you.