Grouping sales by customer percentile

[Sarunas]

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.

[AlB]

@Sarunas 

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 

[AlB]

Hi @Sarunas 

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 

[Sarunas]

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.