Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Sreeram
Regular Visitor

Count of customers contributing to 80% of revenue and slice and dice data

‎04-05-2017 01:12 PM

Hi Guys. I would like to add a calculated column in PowerBI which tells wheather a particular dimension is part of top 80% or bottom 20% of the value. For e.g. I have customer data, say 1000s of them across a number of countries with revenue and other dimensions in table like sales manager, order count etc. I would like to create a chart which shows the count of customers contributing to top 80% revenue and bottom 20% revenue. I created a column in excel indicating top 80% customer and bottom 20% customer and created a chart, but then I realize that I am not able to filter, slice and dice the data as the tags are hardcoded in excel. What is the best formula to achieve this? The outcome I am expecting is show a customer as "Top 80% customer" or "Bottom 20% customer" in a column, but then it has to autocalculate when I filter by other dimensions like product count, sales manager etc. 

 

Kindly direct me to right formula and I am keen to try it out. 

 

Thank you in advance ! 

Message 1 of 7
8,330 Views
0
1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

‎04-05-2017 06:34 PM

I'm not quite certain about your requirement however this should give you some ideas on how to proceed...

You are asking for 2 different things

First Customer CATEGORY (this is a Measure)

Customer Category =
SWITCH (
    TRUE (),
    [MEASURE] < PERCENTILEX.INC ( ALLSELECTED ( Table[Customer] ), [MEASURE], 0.20 ), "Bottom 20%",
    [MEASURE] > PERCENTILEX.INC ( ALLSELECTED ( Table[Customer] ), [MEASURE], 0.80 ), "Top 80%",
    "Middle"
)

And also you want to display COUNT of Customers (this is also a Measure)

Number of Top 80% Customers =
CALCULATE (
    DISTINCTCOUNT ( Table[Customer] ),
    FILTER (
        ALLSELECTED ( Table[Customer] ),
        [MEASURE] > PERCENTILEX.INC ( ALLSELECTED ( Table[Customer] ), [MEASURE], 0.80 )
    )
)

Following the above example you should be able to create the Number of Bottom 20% Customers Measure

Hope this helps! Smiley Happy

View solution in original post

Message 4 of 7
9,638 Views
0
6 REPLIES 6
Sean
Community Champion
Community Champion

‎04-05-2017 06:34 PM

I'm not quite certain about your requirement however this should give you some ideas on how to proceed...

You are asking for 2 different things

First Customer CATEGORY (this is a Measure)

Customer Category =
SWITCH (
    TRUE (),
    [MEASURE] < PERCENTILEX.INC ( ALLSELECTED ( Table[Customer] ), [MEASURE], 0.20 ), "Bottom 20%",
    [MEASURE] > PERCENTILEX.INC ( ALLSELECTED ( Table[Customer] ), [MEASURE], 0.80 ), "Top 80%",
    "Middle"
)

And also you want to display COUNT of Customers (this is also a Measure)

Number of Top 80% Customers =
CALCULATE (
    DISTINCTCOUNT ( Table[Customer] ),
    FILTER (
        ALLSELECTED ( Table[Customer] ),
        [MEASURE] > PERCENTILEX.INC ( ALLSELECTED ( Table[Customer] ), [MEASURE], 0.80 )
    )
)

Following the above example you should be able to create the Number of Bottom 20% Customers Measure

Hope this helps! Smiley Happy

Message 4 of 7
9,639 Views
0
PaulDBrown
Community Champion
Community Champion
In response to Sean

‎06-30-2019 01:48 AM

@Sean 

I’m trying to understand your measure for “number of top 80% customers” and how the function PERCENTILEX.INC works. Seeing how the bottom 20% measure is written, won’t the top 80% measure actually deliver the top 20%?.

in other words, shouldn’t the measure to count the top 80% be

CALCULATE (

DISTINCTCOUNT ( Table [Customer] ),

FILTER (ALLSELECTED ( Table [Customer] ),

[MEASURE] > PERCENTILEX.INC (ALLSELECTED ( Table [Customer] ), [MEASURE], 0.20 ) 

which would exclude the bottom 20%?

 

Apologies upfront since it is the first time I have actually come across the PERCENTILEX.INC function. You can tell I’m a little confused!

 

Thanks!





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Message 7 of 7
6,017 Views
0
Anonymous
Not applicable
In response to Sean

‎06-28-2019 09:54 AM

Hello, 

 

I am not able to develop to 

Number of Top 80% Customers

When I am writing the measure appears that PERCENTILE.INC have too few arguments. 

Can you help me please?

I wrote: 

 

CALCULATE(
DISTINCTCOUNT('Table'[Column]);
FILTER(
ALLSELECTED ('Table'[Column]);
[Measure]>PERCENTILE.INC (ALLSELECTED('Table'[Column]);[Measure];0,90)))

 

Message 6 of 7
6,024 Views
0
Sreeram
Regular Visitor
In response to Sean

‎04-10-2017 10:26 PM

Thanks Sean.. You must be a genius !

 

I will try this out. Appreciate your input. 

Message 5 of 7
8,257 Views
0
Phil_Seamark
Employee
Employee

‎04-05-2017 04:11 PM

HI @Sreeram

 

Some sample data might be helpful.  Also this can be done as a calcuated column or a calcuated measure.  Each approach as different strengths and weaknesses.  

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Message 2 of 7
8,316 Views
0
Sreeram
Regular Visitor
In response to Phil_Seamark

‎04-10-2017 10:24 PM

Hi Phil, Thanks for your response.. Here is how the data could look like.. not sure where to attach this. So, copy pasting it here. Now, on this table, i would like to add another column called Customer Group with values "Top 80%" and "Bottom 20%". The groups obviosly has to be Auto-calculated using the revenue figures and should get recalculated when we filter by country (s). 

 

CustomersRevenueCountry
a11301USA
b1610USA
c16110USA
d1155USA
e10765USA
f1500USA
g14280USA
h18238USA
i15238USA
j3336USA
k9525USA
l10561USA
m2356USA
n2926USA
o13777USA
p10976USA
q5201USA
r14498USA
s9545USA
t11098USA
u3677USA
v15259USA
w13213USA
x8866UK
y7860UK
z5239UK
a1424UK
b15785UK
c16220UK
d1609UK
e10132UK
f10449UK
g14930UK
h3612UK
i13157UK
j16352UK
k8083UK
l16405UK
m14870UK
n1999UK
o15612UK
p16773UK
q3937UK
r3146UK
s13796UK
t18189UK
u6502UK
v5419UK
w3174UK
x8544UK
y9413UK
z12751UK
b12413UK
c15177UK
d14567UK
e11961UK
f9223UK
g7131UK
h13663UK
i11466UK
j3379UK
k13380UK
l5475UK
m3514UK
n16942UK
o18707UK
p13596UK
q12544UK
r6943India
s6848India
a7366India
b2129India
c14981India
d9033India
e13097India
f8710India
g18165India
h15658India
i2701India
j15871India
k2000India
l10192India
m4356India
n1998India
o14126India
p7455India
q17172India
r10119India
s1512India
t7219India
u1236India
v13803UAE
w13340UAE
x12497UAE
y18269UAE
z17554UAE
a18743UAE
b17816UAE
c8272UAE
d10841UAE
e2039UAE
f16294UAE
g12700UAE
h5429UAE
i11608UAE
j18841UAE
k3961UAE
l13205UAE
m7225UAE
n17641UAE
o10872UAE
p3880UAE
q11737UAE
r11747UAE
s11597UAE
t10646UAE
u13644UAE
v10354UAE
w16047UAE
x12177UAE
y4964UAE
z3661UAE
b13380UAE
c18018UAE
d13789UAE
e3782UAE
f16079UAE
g15841UAE
h14269UAE
i1654UAE
j16214UAE
k10116UAE
l14904UAE
m13176UAE
n1993UAE
o1860UAE
p10964UAE
q17709UAE
r14694UAE
s15701UAE
y9717UAE
z5175UAE
a10639UAE
b4153UAE
c15100UAE
d7779UAE
e15051UAE
f18249UAE
g14365UAE
h6922UAE
i17802UAE
j8347UAE
k18422UAE
l7831UAE
m1127UAE
n5396UAE
o17067UAE
p7285UAE
q9433UAE
r6226UAE
s12997UAE
t10941UAE
u2863UAE
Message 3 of 7
8,259 Views
0

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

View All