cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
James452
Regular Visitor

Dynamic Decile grouping performance issues

Hello,

 

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"

let
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"}})
in
#"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)

Return
CALCULATE(
COUNTROWS('Dataset')
,'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
110000
210000
310000
410000

 

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.

2 REPLIES 2
James452
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.

James452_0-1638997294413.png

 

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

sreenathv
Super User
Super User

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

https://www.daxpatterns.com/dynamic-segmentation/ 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

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 May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!