CODEWITHSUNDEEP
powerbidax
joeyops
joeyops

Reputation: 51

Power BI DAX - Dynamically Categorize Column Values

I have a column of the form

  Revenue
    10000
    20000
      500
        1
     3300
      800
       90
       45
      200
       55

I would like to create a new column in my power bi dataset that dynamically buckets these into Top 20%, Top 20-50%, Bottom 20-50% and Bottom 20% revenue contribution based on the range of values in the column .

How do I achieve this with DAX?

Upvotes: 0

Views: 795

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

Not sure what your difference is between Top and Bottom 20-50%. I assume there's a typo.

I'm pretty new to DAX, and I'm not sure if this is exactly how you want to bucket the values, but it seems you could do this with RANKX and a nested IF formula:

Rank =
IF (
    RANKX ( 'Table', [Revenue], [Revenue], ASC )
        >= ( 0.8 * COUNT ( 'Table'[Revenue] ) ),
    "Top 20%",
    IF (
        RANKX ( 'Table', [Revenue], [Revenue], ASC )
            >= ( 0.5 * COUNT ( 'Table'[Revenue] ) ),
        "Top 50%-80%",
        IF (
            RANKX ( 'Table', [Revenue], [Revenue], ASC )
                >= ( 0.2 * COUNT ( 'Table'[Revenue] ) ),
            "Bottom 20-50%",
            "Bottom 20%"
        )
    )
)

I sorted the output to make results easier to see
enter image description here

If you want to bucket using some other algorithm, please clarify.

Upvotes: 1

Related Questions