cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ErikOmni
Helper I
Helper I

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 dataset looks like and what I would like to achieve.

 

ErikOmni_0-1673985886158.png

 

Any ideas?

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

Hi @ErikOmni ,

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

Table:

vbinbinyumsft_0-1674009567274.png

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

vbinbinyumsft_2-1674009675518.png

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.

View solution in original post

3 REPLIES 3
v-binbinyu-msft
Community Support
Community Support

Hi @ErikOmni ,

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

Table:

vbinbinyumsft_0-1674009567274.png

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

vbinbinyumsft_2-1674009675518.png

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
Resident Rockstar
Resident Rockstar

@ErikOmni,

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"
)

@v-binbinyu-msft 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.

Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors