cancel
Showing results for
Did you mean:

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

Frequent Visitor

## Grouping sales by customer percentile

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.

3 REPLIES 3
Super User

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

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

Frequent Visitor

Hi, @AlB ,

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.

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors