Forum Discussion

samirineni's avatar
samirineni
Frequent Visitor
5 years ago

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 ...
Need Help
samirineni's avatar
samirineni
Frequent Visitor
5 years ago

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

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    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!

    • samirineni's avatar
      samirineni
      Frequent Visitor
      5 years ago

      @ 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?

       

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        5 years ago

        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!