Total of Customer revenue by Total revenue and apply IF conditions

[PrathSable]

Hi Guys,

Need a small help on the below calculation -

We need to identify customer segments from the data. Definition for customer segments is – Customers with 0 to 70 % of total revenue are Platinum customers, Customers with 70 to 80 % of total revenue are Gold customers and reaming are Silver customers.

Invoice Number	Location Name	Movement Type	Month	Amount	Customer Name
1	A	Domestic	4	1,40,366	Customer14
2	B	Export	5	1,159	Customer15
3	C	Export	3	579	Customer2
4	B	Domestic	4	7,965	Customer2
5	A	Import	1	7,965	Customer15
6	B	Domestic	3	579	Customer1
7	C	Export	6	7,965	Customer9
8	C	Import	1	579	Customer7
9	B	Export	4	7,965	Customer5
10	B	Export	4	7,965	Customer13
11	B	Export	5	7,965	Customer5
12	B	Export	4	579	Customer11
13	B	Import	4	7,965	Customer6
14	C	Import	1	7,965	Customer13
15	C	Export	2	579	Customer9
16	C	Export	4	7,965	Customer1
17	C	Domestic	5	7,965	Customer14
18	C	Import	4	7,965	Customer2
19	A	Import	5	7,965	Customer13
20	A	Domestic	4	7,965	Customer9
21	A	Export	6	579	Customer11
22	A	Import	5	7,965	Customer6
23	A	Export	4	7,965	Customer15
24	A	Domestic	1	1,159	Customer8

Appreciate your help on this 🙂

Regards,

PrathSable

[v-diye-msft · Accepted solution]

Hi @PrathSable 

let me know if you'd like to get this one;

Measure = var a = CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Customer Name]))
var b = SUMX(ALL('Table'),[Amount])
return 
DIVIDE(a,b)

Measure 2 = SWITCH(TRUE(),[Measure]>=0&&[Measure]<=0.7,"Platinum customers",[Measure]>=0.7&&[Measure]<=0.8,"Gold customers",[Measure]>=0.8,"Silver customers")

Pbix attached.

[v-diye-msft · Accepted solution]

Hi @PrathSable 

let me know if you'd like to get this one;

Measure = var a = CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Customer Name]))
var b = SUMX(ALL('Table'),[Amount])
return 
DIVIDE(a,b)

Measure 2 = SWITCH(TRUE(),[Measure]>=0&&[Measure]<=0.7,"Platinum customers",[Measure]>=0.7&&[Measure]<=0.8,"Gold customers",[Measure]>=0.8,"Silver customers")

Pbix attached.

[harshnathani]

Hi @PrathSable ,

https://community.powerbi.com/t5/Desktop/Customer-Segmentation-based-on-Running-Total-amp-Threshold-...

The solution is given in this post. Similar post I think.

Regards,

Harsh Nathani

[harshnathani · Accepted solution]

Hi @PrathSable ,

Create 2 measures.

Total Amount = CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Customer Name]))

Segment =

var _70p = .70* CALCULATE([Total Amount],ALL('Table'))

var _80p = .80* CALCULATE([Total Amount],ALL('Table'))

var _rank = RANKX(ALL('Table'[Customer Name]),[Total Amount])

var _runningtotal =CALCULATE([Total Amount],FILTER(ALL('Table'), _rank >= RANKX(ALL('Table'[Customer Name]),[Total Amount])))

return

SWITCH(

TRUE(),

_runningtotal<= _70p , "Gold",

_runningtotal > _70p && _runningtotal <= _80p , "Siver",

_runningtotal >_80p, "Bronze"

)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@PrathSable , refer if this can help

Top 80/20 , percent /percentile
https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459
https://finance-bi.com/power-bi-pareto-analysis/
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculate-the-sum-of-the-top-80/td-p/763156

SEGMENTATION

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization