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 = 235
Specific transformation of split column values
In many cases, it involves splitting the value. If it is just a basic splitting of the value, it can be achieved by using Split function in Power query. However, in the following case, the user has different values in each cell with "|" as a separator and needs to remove the separator and sort the values in reverse order based on the date after the separator is removed.
