Forum Discussion

Balapradeep's avatar
Balapradeep
Frequent Visitor
4 years ago
Solved

Creating custom buckets for spend amount

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

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

     

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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.