Forum Discussion

hawb's avatar
hawb
Helper I
5 years ago
Solved

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] )
 
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    5 years ago

    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.

2 Replies