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 