Dynamic calculation for changing Amount

[R_]

Hi All,

How we can create dynamic calculation on changing bucket as :

Row	BucketStatus	BucketAmiount
1	<1	1000
2	1--2	>1000<2000
3	>2	>2000

and in future its change to 

Row	BucketStatus	BucketAmiount
1	<2	2000
2	2--3	>2000<3000
3	>3	>3000

I dont want to change the logic which I have created in BI.

XYZ = SWITCH(TRUE(),'Table'[Amount]<=BucketAmount_FirstRow,"BucketStatus_FirstRow",'Table'[Amount] is between BucketAmount_SecountRow,"BucketStatus_SecondRow" && 'Table'[Amount]<=BucketAmount_ThirdRow,"BucketStatus_ThirdRow")

Or in case I have created bucket, Its shd be dynamic.

Bucket1 = IF(Data[Amount]<=BucketAmount_FirstRow && YEAR(Data[Date])=(MAX('Date'[Year])),"BucketStatus_FirstRow",
IF(Data[Amount]>=BucketStatus_FirstRow && Data[Amount]< BucketStatus_SecondRow && YEAR(Data[Date])=MAX('Date'[Year]),"BucketStatus_SecondRow",
IF(Data[Amount]>BucketStatus_SecondRow && Data[Amount]<BucketStatus_ThirdRow && YEAR(Data[Date])=MAX('Date'[Year]),"BucketStatus_ThirdRow", BLANK()))))

Thanks,

R_

[v-shex-msft · Accepted solution]

Hi @R_,

Based on your rule table, I write a column to auto generate the bucket state.(For my scenario, you only need to write two row to limit the amount range)

Calculate column:

Bucket State =
VAR Min_Amount =
    LOOKUPVALUE (
        'Dim Bucket'[BucketAmiount],
        'Dim Bucket'[Row], MINX ( ALL ( 'Dim Bucket'[Row] ), [Row] )
    )
VAR temp =
    LOOKUPVALUE (
        'Dim Bucket'[BucketAmiount],
        'Dim Bucket'[Row], MAXX ( ALL ( 'Dim Bucket'[Row] ), [Row] )
    )
VAR Max_Amount =
    RIGHT ( temp, LEN ( temp ) - 1 )
RETURN
    IF (
        [Amount] <= VALUE ( Min_Amount ),
        LOOKUPVALUE (
            'Dim Bucket'[BucketStatus],
            'Dim Bucket'[Row], MINX ( ALL ( 'Dim Bucket'[Row] ), [Row] )
        ),
        IF (
            [Amount] >= VALUE ( Max_Amount ),
            LOOKUPVALUE (
                'Dim Bucket'[BucketStatus],
                'Dim Bucket'[Row], MAXX ( ALL ( 'Dim Bucket'[Row] ), [Row] )
            ),
            INT ( [Amount] / 1000 )
                & "--"
                & (
                    INT ( [Amount] / 1000 )
                        + 1
                )
        )
    )

 

Notice: the min and max status are look from bucket range table, other median parts are auto generated.

Regards,

Xiaoxin Sheng