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

Creating Dynamic Equal Bins (using percentile.inc)

 
1.) I have a table containing columns for Fish Species and Length. e.g.
Bluegill 7.7
Bluegill 8.2
Bluegill 13.2
Crappie 7
Crappie 8.2
Crappe 10.3
 
2.) I want to be able to generate a column in my table that will categorize equally sized "Bins" (i.e. quintiles) BY FISH SPECIES.
 
I found a good reference for how to do this here.
 
The problem in this example is that it generates the bins across the entire dataset instead of by Fish Species. How can I dynamically generate these values based on species?
 
Here is my Dax script
EqualBinsBySpecies =
--Quintiles
VAR N = 5
VAR Percentiles =
ADDCOLUMNS (GENERATESERIES( 1, N ),"Percentile", PERCENTILE.INC(page_Summary_SurveyMeasurements[LengthInches], [Value] / N )
)

RETURN
MINX ( FILTER (Percentiles,page_Summary_SurveyMeasurements[LengthInches] <= [Percentile] ), [Value] )
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Hi @hawb ,

 

Try this:

Bucket = 
VAR N = 5
VAR Percentiles =
    ADDCOLUMNS (
        GENERATESERIES ( 1, N ),
        "Percentile", PERCENTILEx.INC ( FILTER('Table',EARLIER('Table'[Fish Species])='Table'[Fish Species]),'Table'[Length. e.g], [Value] / N )
    )
RETURN
    MINX ( FILTER ( Percentiles, 'Table'[Length. e.g] <= [Percentile] ), [Value] )

V-lianl-msft_0-1602039277828.png

 

Best Regards,
Liang
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

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Highlighted
Microsoft
Microsoft

Hi @hawb ,

 

Try this:

Bucket = 
VAR N = 5
VAR Percentiles =
    ADDCOLUMNS (
        GENERATESERIES ( 1, N ),
        "Percentile", PERCENTILEx.INC ( FILTER('Table',EARLIER('Table'[Fish Species])='Table'[Fish Species]),'Table'[Length. e.g], [Value] / N )
    )
RETURN
    MINX ( FILTER ( Percentiles, 'Table'[Length. e.g] <= [Percentile] ), [Value] )

V-lianl-msft_0-1602039277828.png

 

Best Regards,
Liang
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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors