unclesomebody
New Member
- Joined
- Nov 13, 2013
- Messages
- 14
I'm trying to calculate commissions based on stepped or tiered structures. In this case, stepped is defined as similar to an income tax calculation, where each step of income has it's own percentage. Tiered means that once a threshold has been breached the percentage applies to the entire amount.
I found two excellent blog posts on powerpivotpro which deal with both stepped and tiered circumstances.
Tiered: Commission Calculations in PowerPivot Part 2 « PowerPivotPro
Stepped: Tiered Commissions, Income Taxes, and other “Tiered Rate” Calculations in PowerPivot « PowerPivotPro
Note that my nomenclature is the opposite to powerpivotpro. For me, stepped = income tax method!
I've understood the blog posts and I understand the DAX well enough to see roughly what's going on. But the situation I am in is slightly different. Whereas David is using salesvalue as the basis for doing his commissions, I don't have a static value. I have thousands of accounts and each account carries out thousands of transactions each day. In each month I need to have a running total (ie. Month to Date) of the number of transactions per account, and this measure, the number of transactions, is what forms the basis for calculating the commissions. In this particular instance, I want to solve the tiered issue first (ie. non income tax method!).
As an example, the tiering for a particular account may be 0-1000,1000-10000,10000-10000000 txns, with a commission payable of 0.10, 0.12, 0.14. So, if in one month the account has 999 transactions then the commission will be 999*0.10. But, if the account has 1001 txns then the commission will be 1001*0.12.
I can't figure out how to have this dynamic measure (count of transactions MTD) as the basis for the lookup on the tiering. I appreciate this is complicated so perhaps I'll put together a demo file and upload it if anyone thinks that would enable them to help me.
Thanks in advance.
I found two excellent blog posts on powerpivotpro which deal with both stepped and tiered circumstances.
Tiered: Commission Calculations in PowerPivot Part 2 « PowerPivotPro
Stepped: Tiered Commissions, Income Taxes, and other “Tiered Rate” Calculations in PowerPivot « PowerPivotPro
Note that my nomenclature is the opposite to powerpivotpro. For me, stepped = income tax method!
I've understood the blog posts and I understand the DAX well enough to see roughly what's going on. But the situation I am in is slightly different. Whereas David is using salesvalue as the basis for doing his commissions, I don't have a static value. I have thousands of accounts and each account carries out thousands of transactions each day. In each month I need to have a running total (ie. Month to Date) of the number of transactions per account, and this measure, the number of transactions, is what forms the basis for calculating the commissions. In this particular instance, I want to solve the tiered issue first (ie. non income tax method!).
As an example, the tiering for a particular account may be 0-1000,1000-10000,10000-10000000 txns, with a commission payable of 0.10, 0.12, 0.14. So, if in one month the account has 999 transactions then the commission will be 999*0.10. But, if the account has 1001 txns then the commission will be 1001*0.12.
I can't figure out how to have this dynamic measure (count of transactions MTD) as the basis for the lookup on the tiering. I appreciate this is complicated so perhaps I'll put together a demo file and upload it if anyone thinks that would enable them to help me.
Thanks in advance.
