Commission Based Sales Calculations for Different Categories

[Anonymous]

I am trying to calculate the sales amount for different rep's and different subcategories within those reps. The issue I am having is there are different tiers within each subcategory for a min/max  (upper/lower tier amount) that have a unique commission amount. I currently have the sales table with all of the sale amounts and their respective rep and subcategory. I then have another table with the CommsBuckets (Rep, subcategory, upper amt, lower amt, tier (1,2,3, etc), and the commission. How do I calculate the sales amount for each tier within each subcategory and carryover any sale amount exceeding the upper bound into the next tier?

[amitchandak]

@Anonymous , Create meausres like these

Measure = sum(Table[sales amount])

Measure with bucket = sumx(filter( values(Table[sales rep]), [Measure] >= Min(subcategory[lower tier amount]) && [Measure] <= Max(subcategory[upper tier amount])), [Measure])

use the second measure with subcategory table

very similar to what I have done here

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k