Return index value based on MAX of another column, by month, within pivot table

Question

I have a data set of tickets, by month, as well as Duration (in minutes) of some actions that take place throughout the course of our tickets.

Ive created a pivot table and added custom measures to find the 50th & 90th percentile, as well as the max for each month.

What I'd ideally like to do is, in the pivot table, have the Max value of each duration displayed (like ive been able to) but have that value that is displayed be a hyperlink based on the corresponding ticket ID. I cannot slice/filter (or I havent been able to) without break the other duration columns and/or month fields in the table.

I asked this yesterday but neglected to create a minimal, reproducible example and instead linked to a spreadsheet. Thinking about it late last night I figured some here may be averse to downloading a rogue .xlsx document. I'm including a sample of the data in a code block below, as well as a screenshot of my pivot table, examples of my existing formulas, but am leaving the link to the .xlsx doc which resides in my google drive, for those who may be open to looking at it.

I've manually written formulas to show what I'm looking to achieve, but Im hoping to find a way to make this dynamically populated within the pivot table.

I am open to other options than a pivot table as I dont need the drill-down granularity of the pivot table, what I do need is to be able to update the RAWData sheet with data for each additional month as we go, and be able to 'Refresh Pivot table' and have the new month included. I've been able to get things working outside of a pivot table, somewhat, using an INDEX function but I cant seem to get it without having to manually define where one month ends and the next starts somehow. But Ideally, id like all the results/calculations (on the calc page) in a dynamic table that will update when I add additional months of data.

Additionally, I'd like to use the REPLACE function (with IF(ISERROR)) to remove the co/ with rather than use the &Right(xx,LEN(xx)-3) function. In the future Im looking to get the data source of the RAW data to update and omit the co/ (which I would prefer in the long term, for multiple use cases) and if I use LEN, i'll have to go through and update all of my formulas or lose the TIC from the ID field which will effectively break the hyperlinking.

The workbook/.xlsx file contains two sheets. One titled 'calcs' which contains the pivot table and my mock-up of the desired result, the other titled 'RAWData' containing the dataset I'm using. I formatted the data as a Table titled 'RAW'.

"Count of ID" is just using the pivot table's native Count of ID feature. "p50 of" & "p90 of" custom measures within the pivot table use the formula: =PERCENTILE.INC([DurationX],0.5) or .09 respectively, where [DurationX] changes based on the column we're looking at. I set up each of these individually. "Max of Duration" is just using the pivot table's native MAX of [DurationX] feature, where [DurationX] changes based on the column we're looking at.

The "Ideal Max of DurationX" below the main pivot table is the example of how I would like the results in the "Max of DurationX" column above it to be returned. It displays the max value just like the pivot table above does, but creates a hyperlink, based on the corresponding ID of that max value. I've manually created this formula to achieve this, but I had to go through and :

=HYPERLINK("https://someurl.com/"&RIGHT(RAWData!A9,LEN(RAWData!A9)-3),RAWData!B9)

I have found an example of using INDEX and MATCH(MAXIFS do this but it requires me to set up a completely separate table apart from the pivot table which, will work if its the only solution, but is not ideal. This formula currently is:

=INDEX(RAW[ID],MATCH(MAXIFS(RAW[PreDetection],RAW[Date/Time(in UTC)],">="&A3,RAW[Date/Time(in UTC)],"<"&A14),RAW[PreDetection],0)) where A3:A14 are the months 1/1/2023:12/1/2023 (formatted mmm)

Here is the content of RAWData sheet (in CSV format)

ID,Duration1,Duration2,Duration3,Duration4,Duration5,Duration6,Duration7,Date/Time(in UTC)
co/TICKET-0001,2.22,0.63,0.63,37,122,147.49,267.27,1/4/23 7:41
co/TICKET-0002,3.23,2.27,2.27,28,121.17,23.61,141.55,1/5/23 20:17
co/TICKET-0003,5.21,0.78,0.78,10,14.13,39.86,48.78,1/7/23 1:50
co/TICKET-0004,2.23,1.95,1.95,12,58.17,40.71,96.64,1/9/23 5:16
co/TICKET-0005,5.23,0.53,0.53,18,25.47,9.84,30.08,1/10/23 18:30
co/TICKET-0006,5.21,0.97,0.97,4,8,56.24,59.04,1/13/23 9:40
co/TICKET-0007,2.23,2.18,2.18,10,13,197.36,208.14,1/13/23 18:58
co/TICKET-0008,7.22,0.43,0.43,8,21,138.62,152.4,1/18/23 16:40
co/TICKET-0009,4.6,0.37,0.37,3,5,101.77,102.17,1/18/23 22:05
co/TICKET-0010,3.2,1.2,1.2,14,29,18.67,44.47,1/19/23 11:31
co/TICKET-0011,5.24,0.6,0.6,11,65,37.91,97.66,1/23/23 10:19
co/TICKET-0012,4.23,0.73,0.73,8,11.15,243.75,250.67,1/24/23 2:10
co/TICKET-0013,3.25,1.3,1.3,4,6,72.55,75.3,1/24/23 10:07
co/TICKET-0014,6.24,2.43,2.43,11,12.22,37.65,43.62,1/24/23 23:55
co/TICKET-0015,3.23,0.83,0.83,6,8,40.12,44.9,1/25/23 0:38
co/TICKET-0016,6.22,0.35,0.35,6,49,25.25,68.03,1/26/23 16:33
co/TICKET-0017,6.24,1.85,1.85,2,12.82,94.3,100.87,1/30/23 6:53
co/TICKET-0018,3.23,0.62,0.62,5,9,111.63,117.41,1/30/23 16:33
co/TICKET-0019,4.22,0.68,0.68,8,42,62.41,100.19,2/1/23 20:35
co/TICKET-0020,5.21,1.77,1.77,2,1,28.13,28.92,2/2/23 19:03
co/TICKET-0021,5.22,0.68,0.68,6,10,35.47,40.26,2/3/23 13:04
co/TICKET-0022,3.22,0.48,0.48,10,15.22,66.48,78.48,2/3/23 15:02
co/TICKET-0023,11.21,0.67,0.67,8,53.35,193.27,235.41,2/4/23 6:28
co/TICKET-0024,11.21,0.78,0.78,6,15.18,130.07,134.04,2/4/23 15:07
co/TICKET-0025,3.26,0.42,0.42,6,12,31.07,39.8,2/6/23 2:36
co/TICKET-0026,5.24,0.7,0.7,7,17.57,52.11,64.44,2/6/23 15:05
co/TICKET-0027,3.2,1.1,1.1,3,5,212.66,214.46,2/6/23 20:22
co/TICKET-0028,5.21,0.58,0.58,3,8,170.49,173.28,2/7/23 8:30
co/TICKET-0029,4.22,0.62,0.62,6,14,43.71,53.49,2/8/23 21:46
co/TICKET-0030,2.21,4.62,4.62,61,71,1261.76,1330.55,2/9/23 12:11
co/TICKET-0031,5.25,0.32,0.32,7,49,52.24,96,2/9/23 17:05
co/TICKET-0032,2.21,0.78,0.78,11,14.23,48.62,60.64,2/10/23 17:16
co/TICKET-0033,3.2,0.45,0.45,4,7,35.72,39.52,2/12/23 0:42
co/TICKET-0034,3.22,0.8,0.8,4,9,35.88,41.66,2/12/23 2:20
co/TICKET-0035,3.18,0.65,0.65,6,9.8,54.13,60.76,2/12/23 10:32
co/TICKET-0036,3.22,2.03,2.03,4,6,32.7,35.48,2/13/23 1:18
co/TICKET-0037,2.22,0.5,0.5,9,22,58.28,78.05,2/13/23 9:56
co/TICKET-0038,3.22,0.75,0.75,4,6,25.61,28.39,2/13/23 14:20
co/TICKET-0039,3.22,0.23,0.23,12,21.48,28.57,46.84,2/13/23 19:00
co/TICKET-0040,5.23,0.6,0.6,5,62.25,32.57,89.59,2/13/23 21:54
co/TICKET-0041,2.25,0.62,0.62,6,9,37.78,44.53,2/14/23 6:13
co/TICKET-0042,5.23,0.28,0.28,7,70.87,54.22,119.86,2/16/23 23:01
co/TICKET-0043,2.23,4.72,4.72,11,31,59.25,88.02,2/21/23 1:19
co/TICKET-0044,6.28,0.67,0.67,2,7,83.02,83.74,2/22/23 5:18
co/TICKET-0045,5.23,1.2,1.2,7,14,218.17,226.94,2/22/23 19:55
co/TICKET-0046,5.24,0.75,0.75,14,79.88,35.44,110.08,2/23/23 0:36
co/TICKET-0047,2.25,2.43,2.43,5,6,74.14,77.89,2/23/23 18:43
co/TICKET-0048,5.25,0.8,0.8,6,11,291.39,297.15,2/25/23 8:39
co/TICKET-0049,6.24,0.72,0.72,6,12.47,22.96,29.18,2/25/23 22:13
co/TICKET-0050,3.23,0.88,0.88,3,5,68.32,70.08,2/27/23 5:44
co/TICKET-0051,2.25,2.02,2.02,8,10.4,103.58,111.73,2/27/23 13:43
co/TICKET-0052,2.26,0.65,0.65,6,7.2,24.74,29.68,2/27/23 17:40
co/TICKET-0053,5.24,0.7,0.7,3,27,61.96,88.73,2/27/23 17:53
co/TICKET-0054,5.23,0.6,-1,4,8,59.32,62.09,3/1/23 3:28
co/TICKET-0055,7.22,1,117,6,132,32.69,157.47,3/3/23 2:33
co/TICKET-0056,5.33,0.27,1,6,13,94.13,101.8,3/3/23 22:24
co/TICKET-0057,4.28,0.72,-1,9,13.2,209.34,218.26,3/4/23 11:01
co/TICKET-0058,9.27,0.13,-4,7,12.4,63.18,66.31,3/6/23 16:57
co/TICKET-0059,7.26,0.75,-2,6,12,11.35,16.09,3/6/23 23:16
co/TICKET-0060,3.24,0.57,10,5,19,136.46,152.22,3/8/23 17:40
co/TICKET-0061,5.27,0.9,0,5,12.48,21.7,28.91,3/9/23 1:29
co/TICKET-0062,5.24,0.82,53,13,73.17,34.96,102.89,3/9/23 17:40
co/TICKET-0063,2.24,1.75,8,57,69,208.75,1160.54,3/10/23 3:23
co/TICKET-0064,5.23,0.4,48,5,59.23,8.5,62.51,3/10/23 12:47
co/TICKET-0065,15.17,0.43,24,25,65.22,59.6,109.65,3/14/23 14:00
co/TICKET-0066,5.25,0.53,49,2,57,100.92,152.68,3/16/23 20:24
co/TICKET-0067,2.26,3.53,25,48,75.03,198.11,270.88,3/17/23 5:53
co/TICKET-0068,5.24,0.85,-2,9,13.45,174.52,182.73,3/17/23 10:03
co/TICKET-0069,3.27,0.58,0,3,6,54.35,57.08,3/18/23 8:30
co/TICKET-0070,8.25,1.33,1,3,14,34.05,514.26,3/19/23 16:51
co/TICKET-0071,3.27,2.02,-2,8,11,227.61,235.33,3/19/23 17:03
co/TICKET-0072,5.22,0.65,-1,4,8,235.54,238.32,3/22/23 8:24
co/TICKET-0073,5.25,0.52,96,9,111.15,82.76,188.66,3/23/23 17:04
co/TICKET-0074,7.25,0.68,0,5,82,15.04,89.79,3/24/23 18:50
co/TICKET-0075,2.27,0.35,8,4,15,133.32,146.05,3/25/23 7:28
co/TICKET-0076,5.24,0.65,0,7,13,41.66,49.41,3/29/23 19:20
co/TICKET-0077,3.24,0.95,-1,34,37,107.77,141.53,3/30/23 6:18
co/TICKET-0078,6.27,0.85,-1,5,11.65,47.14,52.53,3/30/23 7:44
co/TICKET-0079,10.26,0.58,0,8,37.17,38.77,65.68,3/31/23 15:20
co/TICKET-0080,3.25,1.13,-2,3,5,182.74,184.5,4/1/23 1:19
co/TICKET-0081,2.26,0.42,-1,3,4,121.27,123.01,4/3/23 11:28
co/TICKET-0082,3.29,0.97,-1,3,6,111.73,114.44,4/4/23 1:13
co/TICKET-0083,5.23,0.7,31,2,39,43.28,77.05,4/4/23 5:12
co/TICKET-0084,4.29,0.88,0,130,151.43,4557.99,4705.13,4/7/23 7:13
co/TICKET-0085,2.25,0.47,4,8,15,51.25,64,4/10/23 21:11
co/TICKET-0086,7.24,0.32,0,4,14.77,94.28,101.81,4/12/23 19:16
co/TICKET-0087,7.24,0.23,-2,8,13.68,25.69,32.13,4/16/23 4:07
co/TICKET-0088,5.26,0.47,4,9,19.17,70.67,84.58,4/20/23 18:10
co/TICKET-0089,2.24,0.27,14,8,25,18.61,41.37,4/20/23 22:03
co/TICKET-0090,5.24,0.63,87,8,101,30.94,126.7,4/23/23 0:06
co/TICKET-0091,7.25,1.03,-3,8,13.13,96.34,102.22,4/23/23 0:38
co/TICKET-0092,2.29,0.4,15,5,23,47.03,67.74,4/24/23 10:04
co/TICKET-0093,5.28,0.2,6,7,19,13.37,27.09,4/24/23 17:13
co/TICKET-0094,6.3,0.58,-2,8,12.87,36.04,42.61,4/24/23 20:20
co/TICKET-0095,3.28,0.7,3,6,13,41.54,51.26,4/24/23 21:02
co/TICKET-0096,3.25,0.8,0,6,9,159.06,164.81,4/25/23 13:23
co/TICKET-0097,2.26,0.38,1,9,11,235.2,245.94,5/1/23 23:17
co/TICKET-0098,5.25,1.08,258,7,272.25,37.2,304.2,5/3/23 8:19
co/TICKET-0099,8.25,0.78,-1,3,11.17,54.25,57.17,5/3/23 9:56
co/TICKET-0100,11.27,0.22,-2,2,11.9,42.65,43.28,5/4/23 19:11
co/TICKET-0101,3.25,0.95,-2,44,47,227.03,270.78,5/8/23 13:43
co/TICKET-0102,3.24,2.2,12,9,27,100.09,123.85,5/9/23 17:08
co/TICKET-0103,3.28,2.07,1,8,13,36.55,46.27,5/11/23 7:26
co/TICKET-0104,6.25,0.5,95,54,156.48,0.67,150.9,5/12/23 21:50
co/TICKET-0105,6.25,0.6,108,56,171.23,33.39,198.38,5/18/23 2:21
co/TICKET-0106,4.29,0.53,0,18,23,66.02,84.73,5/18/23 16:32
co/TICKET-0107,5.28,0.55,45,9,60.15,13.98,68.85,5/18/23 20:32
co/TICKET-0108,5.28,0.58,34,25,65.18,39.03,98.93,5/18/23 21:12
co/TICKET-0109,5.23,0.73,14,4,24,191.51,210.28,5/20/23 15:52
co/TICKET-0110,3.3,0.33,-1,8,10,55.35,62.05,5/22/23 18:09
co/TICKET-0111,4.25,0.3,28,70,103.25,22.55,121.54,5/24/23 4:53
co/TICKET-0112,3.45,0.47,8,10,22.22,53.57,72.33,5/24/23 16:50
co/TICKET-0113,7.26,1.45,0,3,11,128.54,132.28,5/25/23 9:49
co/TICKET-0114,7.27,0.9,0,4,12,96,100.73,5/25/23 13:10
co/TICKET-0115,6.27,0.98,1,3,12.2,121.81,127.74,5/27/23 8:54
co/TICKET-0116,2.26,0.82,0,9,12.27,89.95,99.96,5/29/23 7:22
co/TICKET-0117,7.28,0.65,29,5,42,19.42,54.14,5/30/23 22:27
co/TICKET-0118,6.24,0.82,-2,7,12.85,120.09,126.7,6/1/23 1:26
co/TICKET-0119,5.31,0.63,44,55,105,37.02,136.71,6/2/23 9:00
co/TICKET-0120,5.25,0.98,14,8,29.18,74.25,98.18,6/3/23 0:38
co/TICKET-0121,7.25,0.42,84,8,100,99.4,192.15,6/5/23 3:22
co/TICKET-0122,6.26,0.33,13,9,29,39.84,62.58,6/5/23 14:13
co/TICKET-0123,5.27,0.35,2,9,17.22,53.5,65.45,6/6/23 3:16
co/TICKET-0124,5.24,0.83,24,17,48.9,115.13,158.78,6/6/23 12:11
co/TICKET-0125,5.27,0.33,54,24,84.18,24.29,103.2,6/6/23 12:32
co/TICKET-0126,2.29,0.27,2,9,12,152.69,162.4,6/10/23 4:46
co/TICKET-0127,4.27,0.63,1,6,12,44.14,51.87,6/14/23 21:29
co/TICKET-0128,4.31,0.42,0,77,81.77,195.99,273.44,6/16/23 21:28
co/TICKET-0129,5.31,0.68,35,3,45,56.54,96.23,6/19/23 19:27
co/TICKET-0130,3.25,0.47,11,34,49.43,246.07,292.24,6/20/23 3:52
co/TICKET-0131,3.28,0.25,1,2,7,63.57,67.29,6/21/23 17:37
co/TICKET-0132,5.27,0.42,4,6,16.35,16.76,27.84,6/22/23 23:21
co/TICKET-0133,3.27,0.57,22,12,38,67.21,101.94,6/23/23 22:08
co/TICKET-0134,3.28,0.52,0,7,11,89.47,97.18,6/25/23 13:59
co/TICKET-0135,6.27,1.53,-1,6,12.97,10.96,17.65,6/29/23 17:35
co/TICKET-0136,6.27,1.43,1,3,12.35,123.7,129.78,6/30/23 0:16
co/TICKET-0137,5.26,1.07,8,14,29.25,22.19,46.19,6/30/23 12:12
co/TICKET-0138,2.31,2.93,50,140,195.25,401.02,593.96,6/30/23 15:26

Google Docs link to .xlsx file: https://docs.google.com/spreadsheets/d/1F919nbp8GucZaYfs45lVnN7hZtcuRciD/edit?usp=sharing&ouid=110428622428089304236&rtpof=true&sd=true

Answer 1

You used a Table but defined it for the entirety of columns A through I on sheet 'RawData'.

Instead, define it only on the used rows. When you paste rows at the bottom of the table, the table range will expand automatically.

I'm pretty sure there's no way you can get a hyperlink in a pivot table.

Let's try a different way using Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="RAW"]}[Content],
    UseStartOfMonth = Table.TransformColumns(Source,{{"Date/Time(in UTC)", Date.StartOfMonth, type datetime}}),
    RenameToStartOfMonth = Table.RenameColumns(UseStartOfMonth,{{"Date/Time(in UTC)", "StartOfMonth"}}),
    ConvertToDate = Table.TransformColumnTypes(RenameToStartOfMonth,{{"StartOfMonth", type date}}),
    UnpivotDuration = Table.UnpivotOtherColumns(ConvertToDate, {"ID", "StartOfMonth"}, "Attribute", "Value"),
    Groups = Table.Group(
        UnpivotDuration, 
        {"StartOfMonth", "Attribute"}, 
        {
            {"Count", each Table.RowCount(_), Int64.Type},
            {"P50", each List.Percentile([Value],0.5), type number}, 
            {"P90", each List.Percentile([Value],0.9), type number}, 
            {"Max", each List.Max([Value]), type number}
        }
    ),
    JoinCols = {"StartOfMonth","Attribute"},
    Joined = Table.Join(Groups,JoinCols & {"Max"},UnpivotDuration,JoinCols & {"Value"}),
    AddTicketLink = Table.AddColumn(Joined, "TicketLink", each "'=HYPERLINK(""https://some_url.com/" & [ID] & """," & Text.From([Max]) & ")"),
    RemoveValue = Table.RemoveColumns(AddTicketLink,{"Value","Max","ID"}),
    RenameTicketLinkToMax = Table.RenameColumns(RemoveValue,{{"TicketLink", "Max"}}),
    Sort = Table.Sort(RenameTicketLinkToMax,{{"Attribute", Order.Ascending},{"StartOfMonth",Order.Ascending}}),
    UnpivotMeasures = Table.UnpivotOtherColumns(Sort, {"StartOfMonth", "Attribute"}, "Attribute.1", "Value"),
    MergeDurationMeasure = Table.CombineColumns(UnpivotMeasures,{"Attribute", "Attribute.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Measure Name"),
    PivotDurationMeasure = Table.Pivot(MergeDurationMeasure, List.Distinct(MergeDurationMeasure[#"Measure Name"]), "Measure Name", "Value", List.First)
in
    PivotDurationMeasure

That produces this:

The issue with using Power Query is that you can't insert formulas into a cell with a query refresh. Hence, following guidance from here, I've put an apostrophe before each link.

Now you do a find/replace on the table (you can automate this if you choose):

This removes the apostrophe and in effect activates the hyperlink.

You can then format the columns using the 'Hyperlink' preset:

And apply any other formatting you want.

EDIT:

Revised AddTicketLink line to remove the "co/" from the ID field:

AddTicketLink = Table.AddColumn(Joined, "TicketLink", each "'=HYPERLINK(""https://some_url.com/" & Text.Middle([ID],3)) & """," & Text.From([Max]) & ")"),