Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello!
I'm looking to develop a few measures that will calculate the range of splits by a commission tier for specific transactions with the following table.
Tier | Min | Max | Spit A % | Split B % |
A | 0 | 10000 | 0.9 | 0.1 |
B | 10001 | 30000 | 0.4 | 0.6 |
C | 30001 | 100000 | 0.6 | 0.4 |
D | 100001 | 500000 | 0.6 | 0.4 |
E | 500001 | 0.8 | 0.2 |
However, the ability to change the range of each Pricing Tier and the corresponding splits percentages between the two groups would be really ideal. E.g, being able to dynamically adjust the tiers: Tier A: 0-25k, Tier B-25k-60k and so on, but also the split %s that would add up to 100% for each Tier.
E.g., Intuitively, I'm thinking it'd be two different measures, $ Split A and $ Split B that I'd use to bring into a table of transaction data. So, let's say you have a transaction amount of $50,000 based on the table example above:
- $10k is split on the first tier
- $20k is split on the second
- $30k is split on the third tier.
I'm thinking there'd have to be a generalized range of potential values for each tier in addition to the potential range of values for the splits percentages, but I'm not exactly sure.
At a minimum, it'd be great to just have the table above, but any ideas or suggestions would be greatly appreciated!
Solved! Go to Solution.
If I understand it correctly, the value of the aggregated measure should be the SUM of A split totals and B split totals, right? If so, it should be always equal to the total of fees which is used to be splitted.
I'm not sure what your [Fee Splits Total ] measure's codes are. If it's a simple formula like [A Split Totals] + [B Split Totals] and put in a table visual without Tiers column, it cannot get the correct max or min values for each tier to calculate. Instead, it gets the max and min value over the whole 'Current' table. Always remember that measures are calcualted according to the context they are located in. Every aggregate function will get the result in corresponding context.
In order to get the correct result, you can try a measure like below. I use a variable table to calculate the split totals for each tier and percentage, and add two columns to the variable table. Then calculate the sum of these two columns.
Measure Total =
VAR Amt = SUM ( Data[Fees] )
VAR _table =
ADDCOLUMNS (
'Current',
"A split",
VAR TierMin = 'Current'[Min]
VAR TierMax = 'Current'[Max]
VAR EligibleFeeA =
SWITCH (
TRUE (),
Amt > TierMax && NOT ( ISBLANK ( TierMax ) ), IF ( TierMin = 0, TierMax - TierMin, TierMax - TierMin + 1 ),
Amt > TierMin, Amt - TierMin + 1,
Amt < TierMin, 0
)
RETURN
EligibleFeeA * 'Current'[A],
"B split",
VAR TierMin = 'Current'[Min]
VAR TierMax = 'Current'[Max]
VAR EligibleFeeB =
SWITCH (
TRUE (),
Amt > TierMax && NOT ( ISBLANK ( TierMax ) ), IF ( TierMin = 0, TierMax - TierMin, TierMax - TierMin + 1 ),
Amt > TierMin, Amt - TierMin + 1,
Amt < TierMin, 0
)
RETURN
EligibleFeeB * 'Current'[B]
)
RETURN
SUMX ( _table, [A split] + [B split] )
Download the attachment for details. Kindly let me know if it helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi thank you, I've discovered some additional refinement for this problem.
I'm interested in segmenting these calculated splits totals by a few different individual contributors and then proportionally attributing those splits totals based on the proportion each company fee contributed to the split.
Index | Producer | Tier | Min | Max | Commissions % | Company % |
1 | Person A | 1 | 0 | 250000 | 0 | 1 |
2 | Person A | 2 | 250000 | 325000 | 0.45 | 0.55 |
3 | Person A | 3 | 325000 | 750000 | 0.6 | 0.4 |
4 | Person A | 4 | 750000 | 9999999999 | 0.7 | 0.3 |
5 | Person B | 1 | 0 | 191250 | 0 | 1 |
6 | Person B | 2 | 191250 | 325000 | 0.45 | 0.55 |
7 | Person B | 3 | 325000 | 750000 | 0.6 | 0.4 |
8 | Person B | 4 | 750000 | 999999999 | 0.7 | 0.3 |
9 | Person C | 1 | 0 | 10000000 | 1 | 0 |
10 | Person D | 4 | 0 | 250000 | 0 | 1 |
11 | Person D | 2 | 250000 | 325000 | 0.45 | 0.55 |
12 | Person D | 3 | 325000 | 750000 | 0.6 | 0.4 |
13 | Person D | 4 | 750000 | 999999999 | 0.7 | 0.3 |
Index | Fees | Company Name | Person Name | Fee Type | Scenario Type | Date |
1 | 60000 | ABC | Person A | A | 1 | 2022-01-01 |
2 | 51913 | XYZ | Person B | A | 1 | 2022-01-01 |
3 | 70000 | OneTwoThree | Person C | B | 1 | 2022-12-01 |
4 | 90000 | ABC | Person A | A | 1 | 2022-01-01 |
5 | 46000 | XYZ | Person A | A | 1 | 2022-03-01 |
6 | 32000 | OneTwoThree | Person C | B | 1 | 2022-04-01 |
7 | 46000 | ABC | Person A | A | 1 | 2022-01-01 |
8 | 80000 | FourFiveSix | Person D | A | 1 | 2022-01-01 |
9 | 90000 | XYZ | Person B | B | 1 | 2022-01-01 |
10 | 60000 | ABC | Person A | A | 2 | 2022-01-01 |
11 | 51913 | XYZ | Person B | A | 2 | 2022-01-01 |
12 | 70000 | OneTwoThree | Person C | B | 2 | 2022-12-01 |
13 | 90000 | ABC | Person A | A | 2 | 2022-01-01 |
14 | 46000 | XYZ | Person A | A | 2 | 2022-03-01 |
15 | 32000 | OneTwoThree | Person C | B | 2 | 2022-04-01 |
16 | 46000 | ABC | Person A | A | 2 | 2022-01-01 |
17 | 80000 | FourFiveSix | Person D | A | 2 | 2022-01-01 |
18 | 90000 | XYZ | Person B | B | 2 | 2022-01-01 |
So, Person A has 81% of their 484,000 in total fees eligible to be run through Person A's commissions schedule going to Company ABC and only 19% going to XYZ.
Company Name | Person Name | A | B |
ABC | Person A | $392,000 | |
FourFiveSix | Person D | $160,000 | |
OneTwoThree | Person C | $204,000 | |
XYZ | Person A | $ 92,000 | |
XYZ | Person B | $103,826 | $180,000 |
How would this work?
I can't quite tell what your specific question is
Thanks, Alexis. Let me clarify. I'm interested in a DAX measure (or a few) that would automatically calculate the $ splits amounts allocated per category (A & B) per tier (A through E) based on the parameters in the table above. Again, if there was an ability to dynamically adjust or change these parameters, that would be ideal.
Like this?
AmountA =
VAR Amt = [SumAmount]
VAR BucketMin = CALCULATE ( MAX ( Tiers[Min] ), Tiers[Min] < Amt )
VAR SplitA =
MAXX ( FILTER ( Tiers, Tiers[Min] = BucketMin ), Tiers[Split A %] )
RETURN
Amt * SplitA
If not, please show what your desired result would look like.
Thanks for your help, Alexis, it got me thinking about the measure a little different and I've made some progress; here's the code I've come up with so far.
It calculates the total amounts per tier and per split correctly, but the aggregated measure itself used in a table or matrix is wrong (notice fee splits total), and i can't figure out why exactly.
Any ideas?
If I understand it correctly, the value of the aggregated measure should be the SUM of A split totals and B split totals, right? If so, it should be always equal to the total of fees which is used to be splitted.
I'm not sure what your [Fee Splits Total ] measure's codes are. If it's a simple formula like [A Split Totals] + [B Split Totals] and put in a table visual without Tiers column, it cannot get the correct max or min values for each tier to calculate. Instead, it gets the max and min value over the whole 'Current' table. Always remember that measures are calcualted according to the context they are located in. Every aggregate function will get the result in corresponding context.
In order to get the correct result, you can try a measure like below. I use a variable table to calculate the split totals for each tier and percentage, and add two columns to the variable table. Then calculate the sum of these two columns.
Measure Total =
VAR Amt = SUM ( Data[Fees] )
VAR _table =
ADDCOLUMNS (
'Current',
"A split",
VAR TierMin = 'Current'[Min]
VAR TierMax = 'Current'[Max]
VAR EligibleFeeA =
SWITCH (
TRUE (),
Amt > TierMax && NOT ( ISBLANK ( TierMax ) ), IF ( TierMin = 0, TierMax - TierMin, TierMax - TierMin + 1 ),
Amt > TierMin, Amt - TierMin + 1,
Amt < TierMin, 0
)
RETURN
EligibleFeeA * 'Current'[A],
"B split",
VAR TierMin = 'Current'[Min]
VAR TierMax = 'Current'[Max]
VAR EligibleFeeB =
SWITCH (
TRUE (),
Amt > TierMax && NOT ( ISBLANK ( TierMax ) ), IF ( TierMin = 0, TierMax - TierMin, TierMax - TierMin + 1 ),
Amt > TierMin, Amt - TierMin + 1,
Amt < TierMin, 0
)
RETURN
EligibleFeeB * 'Current'[B]
)
RETURN
SUMX ( _table, [A split] + [B split] )
Download the attachment for details. Kindly let me know if it helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
This is fantastic! Thank you so much, super helpful! I did disaggregate the individual splits totals to be able to see the segmented amounts by each tier for both splits amounts however. But still very helpful.
But there's one last thing I'm struggling with a little bit - I'd like to create a table that allows for filtering various scenarios that use different inputs that what I provided for the splits tiers, something dynamic preferably, but i'm not sure how this could work. Some kind of scenario table where it'd be like a series of variables for fields that are then referenced in another variable that creates a table of the fields. and so depending on the scneario (low, med, high) the tiers and values and splits percentages would change, etc.
how would this work?
nevermind, this is super easy, i just added a series of new scenarios and added a single slice filter to cycle through them!!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
91 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
70 | |
67 |