CODEWITHSUNDEEP
powerbidax
BARG
BARG

Reputation: 43

Power BI DAX to split the value in to multiple ranges

I have the data as below.

Product     Channel Account_Id  Max_tier        Balance
2000-0100   Direct  102123625   4999.999        174589.26
2000-0100   Direct  102123625   9999.999        174589.26
2000-0100   Direct  102123625   19999.999       174589.26
2000-0100   Direct  102123625   49999.999       174589.26
2000-0100   Direct  102123625   99999999999.99  174589.26

I am trying to build a report in Power BI, where the Balance should be split into a range that should fit into MAX_TIER.So the output should be as below

Product     Channel Account_Id  Max_tier        Balance     Split_range
2000-0100   Direct  102123625   5000            174589.26   5000
2000-0100   Direct  102123625   10000           0           10000
2000-0100   Direct  102123625   20000           0           20000
2000-0100   Direct  102123625   50000           0           50000
2000-0100   Direct  102123625   99999999999.99  0           89589.26

Any thought would be much appriciated

Upvotes: 1

Views: 617

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

To create a Split_range calculated column, you need to figure out what the cumulative total of the prior tier is. You can do this as follows:

CALCULATE (
    SUM ( Table1[Max_tier] ),
    ALLEXCEPT ( Table1, Table1[Product] ),
    Table1[Max_tier] < EARLIER ( Table1[Max_tier] )
)

That is, you add prior tiers where the Product is the same.

Having that, you just need to subtract out the cumulative total from balance if that number is positive and less than the current max.

Split_range =
VAR Cumulative =
    CALCULATE (
        SUM ( Table1[Max_tier] ),
        ALLEXCEPT ( Table1, Table1[Product] ),
        Table1[Max_tier] < EARLIER ( Table1[Max_tier] )
    )
RETURN
    IF (
        Table1[Balance] > Cumulative,
        MIN ( Table1[Balance] - Cumulative, Table1[Max_tier] ),
        0
    )

Upvotes: 1

Related Questions