Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sarunas
Frequent Visitor

Grouping sales by customer percentile

Hi,

 

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

Sarunas_0-1606998262978.png

 

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.

3 REPLIES 3
AlB
Super User
Super User

@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 

SU18_powerbi_badge

 

AlB
Super User
Super User

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 

 

SU18_powerbi_badge

Sarunas
Frequent Visitor

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.