Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.