slabs based factor lookup

[mohsinmasood]

Hi i am tring to pull slab based factor from my data

i have total count of 34,635 in one table and slabs on another table now i have to lookup slab rate which is 1.75 based on count of 34,635 which falls under 25001 - 36500.

count will increase every month and every time it should lookup the factor based on total counts

Count Table

Slab rate table

[Irwan · Accepted solution]

hello @mohsinmasood 

please check if this accomodate your need.

1. create calculated column for calculating slab

Slab = 
CALCULATE(
    MAX('Table'[Slab]),
    FILTER(
        'Table',
        'Count'[Category]='Table'[Category]&&
        'Count'[Count]>'Table'[Start]&&
        'Count'[Count]<'Table'[End]
    )
)

2. create calculated column for calculating slab rate

Slab Rate =
CALCULATE(
    MAX('Table'[Slab Rate]),
    FILTER(
        'Table',
        'Count'[Category]='Table'[Category]&&
        'Count'[Count]>'Table'[Start]&&
        'Count'[Count]<'Table'[End]
    )
)

Hope this will help you.

Thank you.

[Anonymous · Accepted solution]

Hi @mohsinmasood 

For your question, here is the method I provided:

Here's some dummy data

“Count Table”

“Slab rate Table”

Create a measure.

Measure slabs = 
var _Category = SELECTEDVALUE('Count Table'[Category])
var _count = SELECTEDVALUE('Count Table'[Count])
RETURN
CALCULATE(
    SELECTEDVALUE('Slab rate Table'[Slab Rate]),
    FILTER(
        ALL('Slab rate Table'),
        'Slab rate Table'[Category] = _Category
        &&
        'Slab rate Table'[Start] <= _count
        &&
        'Slab rate Table'[End] >= _count
    )
)

Or create a column.

Column slabs = 
CALCULATE(
    SELECTEDVALUE('Slab rate Table'[Slab Rate]),
    FILTER(
        ALL('Slab rate Table'),
        'Slab rate Table'[Category] = 'Count Table'[Category]
        &&
        'Slab rate Table'[Start] <= 'Count Table'[Count]
        &&
        'Slab rate Table'[End] >= 'Count Table'[Count]
    )
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Irwan · Accepted solution]

hello @mohsinmasood 

please check if this accomodate your need.

1. create calculated column for calculating slab

Slab = 
CALCULATE(
    MAX('Table'[Slab]),
    FILTER(
        'Table',
        'Count'[Category]='Table'[Category]&&
        'Count'[Count]>'Table'[Start]&&
        'Count'[Count]<'Table'[End]
    )
)

2. create calculated column for calculating slab rate

Slab Rate =
CALCULATE(
    MAX('Table'[Slab Rate]),
    FILTER(
        'Table',
        'Count'[Category]='Table'[Category]&&
        'Count'[Count]>'Table'[Start]&&
        'Count'[Count]<'Table'[End]
    )
)

Hope this will help you.

Thank you.