Creating custom buckets for spend amount

[Balapradeep]

Happy New Year 2022!!

I have a data like this, I need spend buckets that says if the running spend % is with in 25% then it should be "1_Top 25" else....

I should be able to change the row lables to some other filed but the buckets should works the same way based on column total.

Can anyone help me DAX functio

[v-yanjiang-msft · Accepted solution]

Hi @Balapradeep ,

According to your description, in your snapshot, the [cummilative spend%] is calculated according to the value of [Sum of Spend Amount] from large to small, if it is like this, here's my solution.

Create two measures.

Cummilative spend % =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Sum of Spend Amount] >= MAX ( 'Table'[Sum of Spend Amount] )
    ),
    'Table'[Sum of  Spend Amount 2]
)

Spend bucket =
SWITCH (
    TRUE (),
    [Cummilative spend %] <= 0.25, "1_Top 25",
    [Cummilative spend %] > 0.25
        && [Cummilative spend %] <= 0.5, "2_25-50"
)

 Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak]

@Balapradeep , This need segmentation using an independent table, I tried for Margin % measure. See if the same can work for cumulative %

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k