Tiered Transaction Amounts - $ Split Amounts in DAX?

[williamadams12]

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! 

[williamadams12]

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?

[AlexisOlson]

I can't quite tell what your specific question is

[williamadams12]

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. 

[AlexisOlson]

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. 

[williamadams12]

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?

[v-jingzhang · Accepted solution]

Hi @williamadams12 

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.

[williamadams12]

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?

[williamadams12]

nevermind, this is super easy, i just added a series of new scenarios and added a single slice filter to cycle through them!!