Calculate percentages and cummulative percentages within subset of data based on intervall

Hello!

I hope any of you might be able to help me with a problem I can't seem to solve.

I have a data set simplified as follows:

(sorry that the data does not look very neat, but I am trying to make 5 colums and 10 rows)


ID Sum market value of product per ID Category intervalls Percentages of ID within intervall Cummulative percentages within intervall
1 15 000 <250 000 25 % 25 %
2 255 000 >250 000 100 % 100 %
2 255 000 >250 000 100 % 100 %
3 1 000 100 >1 000 000 50 % 50 %
4 45 000 <250 000 25 % 50 %
5 23 000 <250 000 25 % 75 %
6 1 500 000 >1 000 000 50 % 100 %
6 1 500 000 >1 00 0000 50 % 100 %
7 57 000 <250 000 25 % 100 %

There are 5000 of rows of data and one customer ID can have multiple products (and so multiple rows). The sum market value of the product above is the sum of the market values of the different customes per ID.

So I am looking for a formula that calculates the color codes colums above:
- Calculates what percentage the spesific customer is of the total number of customers within that intervall (i.e. not rows, as customers may have multiple rows)
- Calculates the cummulative percentage within the intervall category

I need to create a dashboard where the client can set intervalls and assign the percentage of customers within each intervall to a specific group of customers.

Hope it is clear and that someone can assist.

Best
Tor

Check the yellow banner above.