Running Total along Rank as calculated measure taking too long or out of memory issue

[hutch08]

I hope I can find someone to help with this.

I want to calculate a running total across a calculated measure ranked list of customers. I use a calculated measure for the ranked list so that I can change it dynamically with filters (as opposed to using a calculated column).

the calculated measure for ranking is as follows:

Customer Rank = 
   RANKX(
      ALLSELECTED('Revenue Data'[Customer]),
      CALCULATE(
         IF(
            SUM('Revenue Data'[Quantity]) > 0, 
            SUM('Revenue Data'[Sale Amount])/(SUM('Revenue Data'[Quantity]), 
            0)
      ),,
      ASC
)

The above evaluates fine, but when I try and use the following calculated measure to define the running total of inventory along this ranked customer list it takes a long time to evaluate and sometimes results in out of memory issue.

The Running Total measure is calculated as follows:

Running Total in Customer Rank = 

VAR
   maxRank = [Customer Rank]
RETURN

CALCULATE(
   SUM('Revenue Data'[Quantity]),
   FILTER(
      ALLSELECTED('Revenue Data'[Customer]),
      [Customer Rank] =< maxRank
   )
)

I have tried looking at this in Dax Studio, and it clearly has an issue with this running total measure. I believe it is getting stuck evaluating something that it doesn't need to, but I can't figure out how to drill down deeper and see exactly what is wrong.

Just to bring this together so you understand my end goal, I want to use this running total to plot customer purchases in an XY chart where the-y value is the particular customers Average purchase price, the x value is the running total in quantity purchased at that customer, and from left to right the customers are ordered from lowest average purchase price to highest average purchase price. Example in image below:

[v-juanli-msft]

Hi @hutch08

The second measure invokes another measure which contains specific filter and calculations, it may cause to filters conflict and looping calculation.

Some reference links:

Optimizing DAX expressions involving multiple measures.

Nested Measure Performance

Best Regards

Maggie