Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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! 😀
Solved! Go to Solution.
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])
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])
Don't include the Date Started or the Site ID in the visual.