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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

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.