Scale Data based on first value in a group

[mhectordalhoff]

Hi

I have data on the following form

Category	Date	Value
A	2022-01-01	2
A	2022-01-02	3
A	2022-01-03	1
B	2022-01-01	4
B	2022-01-02	6
B	2022-01-03	5

I wish to dived eatch values with the first value in each group

Category	Date	Value	New Value
A	2022-01-01	2	1
A	2022-01-02	3	1.5
A	2022-01-03	1	0.5
B	2022-01-01	4	1
B	2022-01-02	6	1.5
B	2022-01-03	5	1.25

Further more i have a slicer on the Date column and need it to recalculate if the date slicer is changed. So if the date slicer was change to be after 2022-01-01 the result would be this

Category	Date	Value	New Value
A	2022-01-02	3	1
A	2022-01-03	1	0.3333
B	2022-01-02	6	1
B	2022-01-03	5	0.8333

Hope someone can help me with this. thanks

[CNENFRNL · Accepted solution]

For fun only, showcase of powerful Excel worksheet formula,