Calcucate Commission from a Range Table
Need help to add a calculated column for sales commission in each row of the Sales table. The commission % is determined by the total YTD revenue for the sales person for which the lookup range is in Target table. Example - For Person1 18-Dec-2024 - YTD invoice is 500, so commission @ 5% 13-Jan-2025 - YTD invoice is 2000 (Dec - 500+Jan - 1500), first 500 already paid in Dec, remaining 500 @5% and 1000 @ 6%. 24-Feb-2025 - YTD invoice is 4000 (Dec 500 + Jan - 1500 + Feb 2000) first 2000 already paid, remaining 1500 @ 6% and 500 @ 7% Total = 235Spread costs across months
Hi, I have seen different iterations of this in posts but can't seem to find an option that works for me. I'm trying to calculate the monthly spend on apprentices based on their start and end date. The cost should be attributed as follows: Between start and end date 80% of the apprenticship total should be paid. (divide 80% of total cost by number of months) The month after apprentice ends pay the final 20%. the number of days in the month is irrelivant i.e if there is at least one day in a month it should be added to the total number of months. as an example: A £100 apprenticship that lasts 10 months, ending in September should show: £8 per month for 10 months ending in September £20 spend occuring in October. The tricky bit is the length, start/end and cost can be different everytime. I would like to be able to display this in a matrix or table and a line or column chart, ideally both but will take what I can get if there are limitations for any reason. My data is laid out as follows: Both relationships are inactive Planned end date * - 1 Date Planned start date * - 1 Date The tables are as follows: Period (the date range covers all relevant dates) FactApprentice Duration Months, 80% agreed price, 20% agreed price are calcualted columns. Ideally I would like to be able to do this without adding another table but if required it can be. Please cask for more detail if needed.
