cancel
Showing results for
Did you mean:  ## 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 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

2 ACCEPTED SOLUTIONS  Community Support

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.

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.  Super User

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)

4 REPLIES 4  Community Support

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.

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.  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  Super User

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)  Super User

@PrathSable , refer if this can help

SEGMENTATION

New Power BI Features
Datamarts: https://youtu.be/8tskWsJTEpg
Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Dashboard of My Blogs !! Connect on Linkedin !! Proud to be a Super User!
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
!! Subscribe to my youtube Channel !! Announcements   