cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PrathSable
Advocate II
Advocate II

Total of Customer revenue by Total revenue and apply IF conditions

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 NumberLocation NameMovement TypeMonthAmountCustomer Name
1ADomestic41,40,366Customer14
2BExport51,159Customer15
3CExport3579Customer2
4BDomestic47,965Customer2
5AImport17,965Customer15
6BDomestic3579Customer1
7CExport67,965Customer9
8CImport1579Customer7
9BExport47,965Customer5
10BExport47,965Customer13
11BExport57,965Customer5
12BExport4579Customer11
13BImport47,965Customer6
14CImport17,965Customer13
15CExport2579Customer9
16CExport47,965Customer1
17CDomestic57,965Customer14
18CImport47,965Customer2
19AImport57,965Customer13
20ADomestic47,965Customer9
21AExport6579Customer11
22AImport57,965Customer6
23AExport47,965Customer15
24ADomestic11,159Customer8

 

Appreciate your help on this 🙂

 

Regards,

PrathSable

2 ACCEPTED SOLUTIONS
v-diye-msft
Community Support
Community Support

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")

07.PNG

Pbix attached.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

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"
)
 
 
1.jpg
 

Regards,
Harsh Nathani

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

View solution in original post

4 REPLIES 4
v-diye-msft
Community Support
Community Support

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")

07.PNG

Pbix attached.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
harshnathani
Super User
Super User

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

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"
)
 
 
1.jpg
 

Regards,
Harsh Nathani

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors