Optimize RANKX and CUMULATIVE total to segment customers as top 80% and bottom 20%

[apohl1]

Hi PBI experts,

I am working on an analysis where I want to segment customers based on their opportunity. My aproach is summarized below. The approach works but performance is bad. I'm posting my formulas below, if anyone has an idea how to improve the formulas it would be highly appreciated! If anyone has a different idea how to the analysis with a different approach that's also super welcome! Thanks in advance!

The approach:

1. Rank the customers based on opportunity amount

2. Create cumulative total based on customer rank

3. Segment customers into 2 groups based on their cumulative total ("top 80%" anf "bottom 20" customers")

The measures:

• Opportunity rank =

var blanks = ISBLANK(sum('Procedure volumes'[Opportunity]))||sum('Procedure volumes'[Opportunity])=0

RETURN

SWITCH(

TRUE(),blanks,BLANK(),

RANKX(ALLSELECTED('C Account'[C. Account Name]),CALCULATE(sum('Procedure volumes'[Opportunity])),,DESC,Skip))

• Cumulative =

var currentRank = [Opportunity rank]

var blanks = ISBLANK('Procedure volumes'[Opportunity rank])||sum('Procedure volumes'[Opportunity])=0

RETURN

SWITCH(

TRUE(),

blanks,BLANK(),

SUMX(FILTER(ALLSELECTED('C Account'[C. Account Name]), [Opportunity rank]<=currentRank ), CALCULATE(sum('Procedure volumes'[Opportunity]))))

• Customer classification =

VAR topPercentage = 0.8

VAR totalMargin = CALCULATE(sum('Procedure volumes'[Opportunity]), ALLSELECTED('C Account'[C. Account Name]))

VAR topRankNumber = MAXX(FILTER(ALLSELECTED('C Account'[C. Account Name]), [Cumulative]<= totalMargin* topPercentage), [Opportunity rank] )

RETURN

IF(ISBLANK([Opportunity rank]) || ISBLANK(sum('Procedure volumes'[Opportunity])), BLANK(),

IF( [Opportunity rank]<=topRankNumber,"Top 80%","Bottom 20%"))

[apohl1]

Thanks so much @amitchandak, it works perfectly!!

[amitchandak · Accepted solution]

@apohl1 , Refer this blog- I doubt you need rank, check if this can help

https://exceleratorbi.com.au/pareto-analysis-in-power-bi/