Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
williamadams12
Resolver I
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 MinMaxSpit A %Split B %
A0100000.90.1
B10001300000.40.6
C300011000000.60.4
D1000015000000.60.4
E500001 0.80.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

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] )

042001.jpg

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.

View solution in original post

8 REPLIES 8
williamadams12
Resolver I
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.

 

IndexProducerTierMinMaxCommissions %Company  %
1Person A1025000001
2Person A22500003250000.450.55
3Person A33250007500000.60.4
4Person A475000099999999990.70.3
5Person B1019125001
6Person B21912503250000.450.55
7Person B33250007500000.60.4
8Person B47500009999999990.70.3
9Person C101000000010
10Person D4025000001
11Person D22500003250000.450.55
12Person D33250007500000.60.4
13Person D47500009999999990.70.3

 

IndexFeesCompany NamePerson NameFee TypeScenario TypeDate
160000ABCPerson AA12022-01-01
251913XYZPerson BA12022-01-01
370000OneTwoThreePerson CB12022-12-01
490000ABCPerson AA12022-01-01
546000XYZPerson AA12022-03-01
632000OneTwoThreePerson CB12022-04-01
746000ABCPerson AA12022-01-01
880000FourFiveSixPerson DA12022-01-01
990000XYZPerson BB12022-01-01
1060000ABCPerson AA22022-01-01
1151913XYZPerson BA22022-01-01
1270000OneTwoThreePerson CB22022-12-01
1390000ABCPerson AA22022-01-01
1446000XYZPerson AA22022-03-01
1532000OneTwoThreePerson CB22022-04-01
1646000ABCPerson AA22022-01-01
1780000FourFiveSixPerson DA22022-01-01
1890000XYZPerson BB22022-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 NamePerson NameAB
ABCPerson A $392,000 
FourFiveSixPerson D $160,000 
OneTwoThreePerson C  $204,000
XYZPerson A $  92,000 
XYZPerson B $103,826 $180,000


How would this work?

AlexisOlson
Super User
Super User

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. 

williamadams12_2-1618446298713.png

 

williamadams12_3-1618446374452.png

 

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?

 

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] )

042001.jpg

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!!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.