cancel
Showing results for
Did you mean:
Regular Visitor

## Dynamic calculation for changing Amount

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_

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements