Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cubelist
New Member

Ranking a Set of Rankings!

Hi Community!

 

This is my first post so be gentle!

 

I've been doing some work with both Qlikview and Power BI over the last few months - from a standing start (thanks Google/Power BI folums!). I'm trying to "Rank" the perfomance of a number of my suppliers/vendors on 3 main areas - Delivery, Assurance and Cost.

 

The "Assurance" ranking is the rank of, average of "duration", where duration is the time taken to resolve an issue according to a ticketing system (resolved timestamp - logged timestamp in dd:hh:mm format), from shortest duration (best) to longest duration (worst).

 

The "Cost" Ranking is the ranking of the average of, cost for a similar product across all suppliers, from cheapest (best) to most expensive (worst).

 

The "Delivery" ranking is the ranking of, the average of the period of time taken to deliver a service from start to finish across all suppliers from shortest delivery time (best), to longest delivery time (worst).

 

So something like:

 

Assurance Ranking = rank(avg(ticket_end_time - ticket_start_time), supplier)

 

Cost Ranking = rank(avg(recurring_cost), supplier)

 

Delivery Ranking = rank(avg(delivery_end_date - delivery_order_date), supplier)

 

I THEN need to average and rank these three ranking values to give me an overall ranking. Something like:

 

Overall Ranking = rank(avg(Assurance Ranking + Cost Ranking + Delivery Ranking), supplier)

 

I'm struggling with getting this to work properly and I think my calculations are off somewhere. Here's my Qlikview expression based on some precalculated values:

 

rank(rank(avg(Duration), [Vendor Ref])+rank(avg([Resolution time in hours]), [Vendor Ref])+rank(avg(NonRecurringPrice+RecurringPrice), [Vendor Ref]))

 

..and finally my very basic visualisation in Qlikview:

2017-09-16 09_55_11-Carrier Dashboard Prototype.pngYou'll notice a "Movement" column also which changes the icon in that column based on the movement of that supplier compared to the previous reporting period. I'm less concerned about reproducting that at this point.

 

If someone can help sanitise my thinking and also give me some ideas on how to reporesent the data in a visualisation I'd really appreiciate it.

 

Many thanks in advance!

 

Darren

 

 

1 ACCEPTED SOLUTION

Hi @cubelist,

 

Based on my with the shared pbix file, the formula below should work in your scenario. Smiley Happy

Rank1 = 
RANKX (
    Rankings,
    CALCULATE ( AVERAGE ( 'Quote Data'[RecurringPrice] ) ),
    ,
    ASC,
    DENSE
)
Rank2 = 
RANKX (
    Rankings,
    CALCULATE ( AVERAGE ( 'Incidents Data'[CalcdResTimeHrs] ) ),
    ,
    ASC,
    DENSE
)
Overall Ranking = 
RANKX ( Rankings, ( Rankings[Rank1] + Rankings[Rank2] ),, ASC, DENSE )

c1.PNG

 

Here is the modified pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

4 REPLIES 4
v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @cubelist,

 

Could you post your table structures with sample/mock data, and the expected result against the data? So that we can better assist on this issue. It's better that you could also share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. Smiley Happy

 

Regards

Hi @v-ljerr-msft and many thanks for responding!

 

Here's the link to the Dropbox folder - apologies it took a while to ananymize the data!

 

https://www.dropbox.com/sh/6ogtolwf09j6adb/AAAHw9qeC0M1eW8NPYet8qGOa?dl=0

 

Let me know if you have any other questions.

 

Thanks


Darren

Hi @cubelist,

 

Based on my with the shared pbix file, the formula below should work in your scenario. Smiley Happy

Rank1 = 
RANKX (
    Rankings,
    CALCULATE ( AVERAGE ( 'Quote Data'[RecurringPrice] ) ),
    ,
    ASC,
    DENSE
)
Rank2 = 
RANKX (
    Rankings,
    CALCULATE ( AVERAGE ( 'Incidents Data'[CalcdResTimeHrs] ) ),
    ,
    ASC,
    DENSE
)
Overall Ranking = 
RANKX ( Rankings, ( Rankings[Rank1] + Rankings[Rank2] ),, ASC, DENSE )

c1.PNG

 

Here is the modified pbix file for your reference. Smiley Happy

 

Regards

Perfect that works exactly how I'd liked. Thanks so much!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors