cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Tiered Transaction Amounts - \$ Split Amounts in DAX?

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!

1 ACCEPTED SOLUTION
Community Support

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 =
'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.

8 REPLIES 8
Resolver I

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?

Super User

I can't quite tell what your specific question is

Resolver I

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.

Super User

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.

Resolver I

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?

Community Support

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 =
'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.

Resolver I

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?

Resolver I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.