Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create order value buckets from dataset with order rows

Hi!   I have a dataset with order rows, with a unique ID for each order. I want to be able to create a column/tag for each orderID with a designated order value range.    See below for how the da...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Please try below steps:
    1. below is my test table

    Table:

    2. create a measure with below dax formula

    Order Range =
VAR cur_sale =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        'Table'[OrderID] = SELECTEDVALUE ( 'Table'[OrderID] )
    )
RETURN
    SWITCH (
        TRUE (),
        cur_sale > 1000, ">1000",
        cur_sale > 600
            && cur_sale < 1000, "600-1000",
        cur_sale < 600, "<600"
    )

    3. add a table visual with fields and measure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

SivaMani's avatar
SivaMani
Resident Rockstar
3 years ago

Anonymous,

Create measures like below,

  • Order Value
Order Value = SUM(tablename[Sales])
  • Order Range
Order Range = SWITCH(
TRUE(),
[Order Value] > 1000, "> 1000",
AND([Order Value] > 600, [Order Value] < 1000),"600-1000",
"< 600"
)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Anonymous Hi! Sorry for late reply. The solution worked, however, I realized I need it to be column to allow me to track YoY development into different buckets. 

 

This formula give me a circular reference warning when I try to apply it as a measure.