Most optimal 80-20 Pareto/cumultative aggregation measure for large dataset

[BUserTG]

Maybe not fair to ask with the number of links on it, but interested if anyone has an uber optimized and efficient DAX meaure to perform cumulatative aggregates on a fairly large dataset. Unfortunately as the calculations must be performed member by member, it's extremely intensive on a larger dataset which is to be expected.

This link lays out a reasonable method: https://finance-bi.com/power-bi-pareto-analysis/ using:

Cumulated  = 
var currentRank = [Rank]

RETURN SUMX(FILTER(ALLSELECTED('Sales table'[Product]), [Rank]<=currentRank ), CALCULATE(SUM('Sales table'[Margin])))

But I've found the most performant calculation to be:

Cumulated = 

IF (NOT( ISBLANK( [Sales] )),

FILTER( ALLSELECTED([Product]),

SUM([BaseSalesValue]) <= [Sales]

)

--- where [Sales] is SUM([BaseSalesValue])

My key requirement is to calculate the cumultative amounts and then find at what point I hit 80%. Long shot but thanks to anyone that may have an innnovate DAX approach that's more performant than the above. My likely approach is to pre-aggregate and pre-calculate these things in the data which obviously takes away from flexibility.

[v-kkf-msft]

Hi @BUserTG ,

Does your problem have been solved? If it is not solved, please try the following formula:

Total = SUMX( ALL('Table'), 'Table'[BaseSalesValue] ) * 0.8

_Sum = 
CALCULATE(
    SUM('Table'[BaseSalesValue]),
    FILTER(
        ALL('Table'),
        'Table'[Index] <= MAX('Table'[Index])
    )
)

Result = 
MINX(
    FILTER('Table', [_Sum] >= [Total]),
    'Table'[Index]
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lance_6]

I like using two measures

SalesAmount = SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

CumulativedSales = 

VAR MaxRank = Max(Rank)

VAR Result = 

CALCULATE( [SalesAmount], 

Rank <= MaxRank,

ALL(Rank)

)

RETURN 

Result