Creating Dynamic Equal Bins (using percentile.inc)

[hawb]

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.

https://stackoverflow.com/questions/59989628/equal-bins-in-dax-equivalent-of-ntile-function

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

[V-lianl-msft · Accepted solution]

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

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

[amitchandak]

@hawb ,

Not very Clear. Refer if my Video and file(attached after signature) can help

https://youtu.be/CuczXPj0N-k

Or refer

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization