Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

Dynamic Decile grouping performance issues



I'm working on a report on profitability, and one of the requests was to separate line-items into decile groups (i.e. separate into 10 relatively equal groups with statistics on each group). For the entire population this is relatively simple using a calculated column with RANKX(). However they also want to be able to filter data (such as selecting a single region), and have the Decile grouping for only that filtered data. Unfortunately the number of filters that could potentially be applied is significant, so I'm trying to find a way to create a dynamic decile grouping.


Based on other posts my understanding is that binning and RANKX() won't work since they only calculate at data-refresh. I was able to use a measure with a disconnected "Deciles" table to achieve the result; however it has extremely poor performance in visuals. Up until about 10K rows it works (although can take 40 seconds), but when testing with 100K+ rows I've never gotten it to finalize.


In order to diagnose, I've only been trying to count the number of rows in each Decile.


My example dataset is just a large list of numbers from 0 to 10,000 with 0.1 increments (0.0, 0.1, 0.2, ... , 9999.98, 9999.99) using the following M Query, which creates the table "Dataset" with a single column "Values"

Source = List.Generate(() => 0, each _ <10000, each _ + 0.1),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Values"}})
#"Renamed Columns"


I'm then creating a Deciles table that is just numbers 1-10, increment of 1 (1, 2, ... , 9, 10). Then creating a measure in the Deciles table to count the number of rows in each decile. Of note, I'm actually reversing the order so that Decile 1 = 10th decile and Decile 10 = 1st decile (as requested by the business).

Decile size =
VAR UpperBound = PERCENTILE.INC('Dataset'[Values],1.1 - MIN(Deciles[Decile])/10)
VAR LowerBound = PERCENTILE.INC('Dataset'[Values],1 - MIN(Deciles[Decile])/10)

,'Dataset'[Values] <= UpperBound && 'Dataset'[Values] > LowerBound



Finally I'm trying to show the Decile and Decile Size fields (both from the Deciles table) in a table visual, with no summarization on the Decile field. If I limit the data to 10K rows it will work; however when I expand to 100K it the Visual won't finish updating. This presents an issue because my true data has substantially more data (280K rows with potential to increase).


Below is what I would expect to see in the visual if it finalized.

DecileDecile size


Any help would be appreciated. I feel like this may be beyond the capabilities of Power BI at the moment, but I want to explore all options.

Regular Visitor

From what I saw in this article, it relies on constant segmentation which is hard-coded in the segmentation tables. I need my segmentation information to also update dynamically (i.e. the upper & lower bounds of each segment should change based on the filtered dataset, as the upper & lower bounds are based on percentiles of the filtered dataset).


What I really need is a calculated column that updates upon filtering in order to calculate the upper & lower bounds, but that's not available. What I wrote uses a variable in the measure to calculate the upper & lower bound during visualization, but the performance is very poor. Below is the perfomance analyzer screenshot using hard-coded segmentation, using measures to only calculate the upper & lower bounds, or using a variable to store the upper/lower bounds and calculate segmentations from that.



While the method works for small datasets (only 10K records in the above), I haven't gotten it to work on larger datasets.

Super User
Super User

Please go through this, if you haven't done so. 

Helpful resources

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors