Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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,
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,
Kelly
Did 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.
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,
Kelly
Did 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.
Maybe something like this:
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,
Kelly
Did I answer your question? Mark my post as a solution!
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |