Bucketing based on SUM

[anileshknpowerb]

Hello,

I have to create a bucketing based on SUM calculation.

For Example, 

IF(SUM(COLUMN1)<10, "<10", IF(SUM(COLUMN1)>=10 && SUM(COLUMN1)<=50, "10-50")).

How to achieve this using DAX.

[v-shex-msft · Accepted solution]

Hi @anileshknpowerb,

Any regular about these formatting operations? If this is a case, you can create a category table with 'index', 'from', 'to', 'desc' fields.

Category =
VAR _current =
    CALCULATE (
        SUM ( 'Sample'[Amount] ),
        FILTER ( 'Sample', [Index] = EARLIER ( 'Sample'[Index] ) )
    )
VAR result =
    CALCULATE (
        MAX ( 'Condition'[Desc] ),
        FILTER ( ALLSELECTED ( 'Condition' ), [From] <= _current && [To] > _current )
    )
RETURN
    IF (
        result <> BLANK (),
        result,
        LOOKUPVALUE (
            'Condition'[Desc],
            'Condition'[Index], MAXX ( ALL ( 'Condition'[Index] ), [Index] )
        )
    )

Regards,

Xiaoxin Sheng

[harshnathani]

Hi @anileshknpowerb ,

Create a Calculated Column

New Column =
SWITCH (
    TRUE (),
    'Table'[Column1] < 10, "<10",
    'Table'[Column1] >= 10
        && 'Table'[Column1] <= 50, "10-50"
)

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[anileshknpowerb]

I need to  bucket it based on SUM(COLUMN1) , NOT COLUMN1. How to do this?

[harshnathani]

Hi @anileshknpowerb ,

Share sample date and your expected output.

Regards,

HN

[anileshknpowerb]

Sample Data:

COLUMN1: 

3

17

17

4

8

9

Expected output:

3          "<10"

34        "10-50"

4         "<10"

8        "<10"

9        "<10"

[harshnathani]

HI @anileshknpowerb ,

What is the logic behind aggregating 17 and not other values?

Can you share proper sample data and expected output ?

Regards,

Harsh Nathani