cancel
Showing results for
Did you mean:
Highlighted
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

Hi @hawb ,

Try this:

``````Bucket =
VAR N = 5
VAR Percentiles =
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 2
Highlighted
Super User IV

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

https://youtu.be/CuczXPj0N-k

Or refer

Proud to be a Super User!

Highlighted
Microsoft

Hi @hawb ,

Try this:

``````Bucket =
VAR N = 5
VAR Percentiles =
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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### 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