cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
samirineni
Frequent Visitor

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 10
samirineni
Frequent Visitor

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,
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:

v-kelly-msft_0-1620203484441.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

@v-kelly-msft 

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.

samirineni_0-1620702630016.png

 

 

v-kelly-msft
Community Support
Community Support

Hi  @samirineni ,

 

How to show the peer vendor rank,is it something like below:

v-kelly-msft_0-1619077584400.png

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:

v-kelly-msft_0-1619078129185.png

 

For the related .pbix file,pls see attached.

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

@v-kelly-msft 

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.

samdthompson
Solution Sage
Solution Sage

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.
 
 

 

 

// if this is a solution please mark as such. Kudos always appreciated.

@samdthompson 

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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors