Percentage Based on Same Field Value

Question

I have a flat table like this:

Date	UserID	Reason	Productive?	TotalDuration	Group	Channel
2022-03-20	A	Break	false	00:13:21	Foo	#E
2022-03-20	A	O.Work	true	07:02:29	Foo	#E
2022-03-20	A	Shift	-	07:27:32	Foo	#E
2022-03-20	A	V.Work	true	00:10:08	Foo	#E
2022-03-20	B	Break	false	00:15:41	Foo	#S
2022-03-20	B	Meeting	false	00:00:26	Foo	#S
2022-03-20	B	OU.Work	true	00:03:36	Foo	#S
2022-03-20	B	Shift	-	06:03:41	Foo	#S
2022-03-20	B	V.Work	true	00:42:38	Foo	#S
2022-03-20	C	Break	false	00:30:01	Bar	#E
2022-03-20	C	O.Work	false	00:04:14	Bar	#E
2022-03-20	C	OU.Work	true	00:35:39	Bar	#E
2022-03-20	C	Shift	-	06:09:48	Bar	#E
2022-03-20	C	V.Work	true	01:37:47	Bar	#E

I want to create a pivotable that calculates the percentage of each Reason based on the Shift value of the same column. In other words Reason[Value] divided by Reason[Shift].

Such pivotable will be filtered by Productive and Reason but want to have the flexibility to use any other field as filter as well.

I think my best option would be some DAX measures like this one but I just can't make it work.

I'm open to all solution within Excel toolkit (VBA, PQ, DAX)

Answer 1

Assuming your table is called Table1 you can use the following DAX

Sum of Total Duration

Sum of Total Duration = 
sum(Table1[TotalDuration])

Sum of Total Duration

Percent Total Duration =
VAR ValueswithoutShift =
    CALCULATE (
        [Sum Total Duration],
        Table1[Reason] <> "Shift",
        VALUES ( Table1[Reason] )
    )
VAR Shift =
    CALCULATE (
        [Sum Total Duration],
        Table1[Reason] = "Shift",
        ALLEXCEPT ( Table1, Table1[UserID] )
    )
RETURN
    DIVIDE ( ValueswithoutShift, Shift )

Output

I'm using UserID, Productive? and Reason as Rows. Sum Total Duration and Percent Total Duration as Values.