topic Grouping sales by customer percentile in DAX Commands and Tips

Grouping sales by customer percentile

Anonymous — Thu, 03 Dec 2020 12:38:14 GMT

Hi,

I'm trying to make a chart, similiar to the one below:

The goal is to dynamically categorize the customers into percentiles (top1%, top2% and so on). If we had 1000 customers let's say, I would need 1% to show the combined Volume of top10 customers, 2% to show the combined Volume of the #11-20 customers, and so on.

I have created a table for percentiles (which I use on x axis):

Percentiles = GENERATESERIES(0.01, 1, 0.01)

Then the following measure:

Measure = PERCENTILEX.INC(Sales, [Volume], VALUES('Percentiles'[Percentile]))

However, that only shows n-th Volume (i.e. the Volume of customer that's at 1% mark, at 2% mark, etc. What I need here though, is the aggregate of Volume for all customers between 0-1%, 1-2%, respectively.

I would appreciate your help a lot.

Re: Grouping sales by customer percentile

AlB — Thu, 03 Dec 2020 12:54:52 GMT

Hi Anonymous

Create a new measure using the one you already have:

Measure_cumul = SUMX ( VAR currentPerc_ = MAX ( 'Percentiles'[Percentile] ) RETURN CALCULATETABLE ( DISTINCT ( 'Percentiles'[Percentile] ), 'Percentiles'[Percentile] <= currentPerc_ ), [Measure] )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Grouping sales by customer percentile

Anonymous — Thu, 03 Dec 2020 13:03:08 GMT

Hi, AlB ,

Thanks for the reply.

I tried it out, but cumulative Volume is not exactly what I need here.

The rephrasesd problem is :

If we have 1000 customers, for 1% percentile my 'Measure' returns only the Volume of #10th Customer, while instead I need it to aggregate #1 to #10. For 2% percentile I need it to aggregate #11 to #20 respectively, and so on.

Re: Grouping sales by customer percentile

AlB — Thu, 03 Dec 2020 14:29:19 GMT

Anonymous

Try this. You might have to tweak it a bit:

Measure V2 = VAR currentPerc_ = MAX ( 'Percentiles'[Percentile] ) VAR previousPerc_ = CALCULATE ( MAX ( 'Percentiles'[Percentile] ), 'Percentiles'[Percentile] < currentPerc_ ) VAR currentVol_ = [Measure] VAR previousVol_ = CALCULATE ( [Measure], 'Percentiles'[Percentile] = previousPerc_ ) RETURN SUMX ( FILTER ( ADDCOLUMNS ( Sales, "res_", [Measure] ), [res_] <= currentVol_ && [res_] > previousVol_ ), [res_] )

If it doens't work it would help if you share the pbix with the expected result, or a simplified version with mock data and an example with the expected result

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers