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
matsahiro
Helper II
Helper II

Bin Naming Output

Hello all,

 

I'm currently binning some records. Say I have products that need to be binned by the amount of revenue each product brings in. Revenue is 0 to 100k, where I'll need 10 bins. Right now, the bin outputs of revenue are 0, 10k, 20k, 30k... etc. How do I have the output of binning be 1, 2, 3.... 10 (representing each number bin)? I can not use the switch function as the values of revenue and distribution will change.

 

Should look something like this

 

RevenueRevenue Bin (Don't want)Bin Number (Want)
300001
16,00010,0002
35,00030,0004
56,00050,0006
87,00080,0009

 

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

Hi @matsahiro ,

 

So you should want the effect of dynamic selection.

 

Here's my workaround.

1.Create two what-if parameters.

4.png5.png

 

2.Create a measure.

Bin Number = 
VAR maxbinnum =
    SELECTEDVALUE ( 'Bin Range'[Bin Range] )
VAR maxrev =
    SELECTEDVALUE ( 'Revenue Range'[Revenue Range] ) * 1000
VAR interval =
    DIVIDE ( maxrev, maxbinnum )
RETURN
    ROUNDUP ( DIVIDE ( MAX ( 'Table'[Revenue] ), interval ), 0 )

 

6.png

 

You can check more details from here.

 

 

Best Regards,

Stephen Tao

 

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

4 REPLIES 4
v-stephen-msft
Community Support
Community Support

Hi @matsahiro ,

 

So you should want the effect of dynamic selection.

 

Here's my workaround.

1.Create two what-if parameters.

4.png5.png

 

2.Create a measure.

Bin Number = 
VAR maxbinnum =
    SELECTEDVALUE ( 'Bin Range'[Bin Range] )
VAR maxrev =
    SELECTEDVALUE ( 'Revenue Range'[Revenue Range] ) * 1000
VAR interval =
    DIVIDE ( maxrev, maxbinnum )
RETURN
    ROUNDUP ( DIVIDE ( MAX ( 'Table'[Revenue] ), interval ), 0 )

 

6.png

 

You can check more details from here.

 

 

Best Regards,

Stephen Tao

 

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

 

Thank you very much for your help!

PhilipTreacy
Super User
Super User

Hi @matsahiro 

Not entirely clear.  You mention Distribution but don't say how this affects your calculations.

Is this what you need to create a new column?

Bin = SWITCH(

    TRUE(),

    'Table'[Revenue] < 10000, 1,

    'Table'[Revenue] < 20000, 2,

    'Table'[Revenue] < 30000, 3,

    'Table'[Revenue] < 40000, 4,

    'Table'[Revenue] < 50000, 5,

    'Table'[Revenue] < 60000, 6,

    'Table'[Revenue] < 70000, 7,

    'Table'[Revenue] < 80000, 8,

    'Table'[Revenue] < 90000, 9,

    10
)

bins.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hello Phil, thanks for the reply! The data is constantly changing, meaning the bin sizes will be different each time the data refreshes. Revenue could be 0 to 100k one day and 0 to 200k the next day. I want to make sure that I always have 20 bins in the visual that are labeled 1-20 even with the bin sizes changing. The reason I cannot use switch in that way is that the bin sizing will change.

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.