Ranking a Set of Rankings!

[cubelist]

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:

You'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

[v-ljerr-msft]

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. 

Regards

[cubelist]

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

[v-ljerr-msft · Accepted solution]

Hi @cubelist,

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

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 )

Here is the modified pbix file for your reference. 

Regards

[cubelist]

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