Forum Discussion
Struggling with Ranking
I have a table called 'Sites' with the following columns
| OrganisationID | SiteID | Date Started | Time to Start |
| 4 | 1044 | 28/06/2018 | 38 |
| 4 | 1045 | 25/04/2012 | 10 |
| 4 | 1048 | 10/01/2024 | 40 |
| 3 | 1092 | 12/08/2022 | 0 |
| 3 | 1093 | 05/02/2024 | 50 |
| 6 | 9057 | 01/03/2023 | 6 |
| 7 | 10084 | 04/09/2023 | 7 |
On my report page I have a table visual that contains the following columns:
OrganisationName (from a dimension table that shares OrganisationID)
Average Time to Start (which is the Time to Start column dragged in, and then set to average which is displaying the total average time to start per organisation accurately)
Date Started is on the many side of a *-1 relationship with my date table so that I can filter the page by 'Fiscal Year' which is a column present in the date table.
I am trying to add a column to my table visual that will dynamically rank the rows from lowest to highest total average start up time for each organisation but can't seem to figure this out.
Any help greatly appreciated as always! 😀
Rank = var a = SUMMARIZE(ALLSELECTED('Table'),[OrganisationID],"R",[Average Time to Start]) return rankx(a,[Average Time to Start],,ASC) Average Time to Start = AVERAGE('Table'[Time to Start])
3 Replies
Don't include the Date Started or the Site ID in the visual.
So the end result I am looking for would do this...
OrganisationID Average Time to Start Rank 6 (Big Bill's Buffalo Ranch) 6.00 1 7 (Tiny Tim's Tiger Emporium) 7.00 2 3 (Loud Lucy's Lion Zoo) 25.00 3 4 (Grim Gary's Giraffe Safari) 29.33 4 Rank = var a = SUMMARIZE(ALLSELECTED('Table'),[OrganisationID],"R",[Average Time to Start]) return rankx(a,[Average Time to Start],,ASC) Average Time to Start = AVERAGE('Table'[Time to Start])
