cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BUserTG
Frequent Visitor

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

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.

2 REPLIES 2
v-kkf-msft
Community Support
Community Support

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

 image.png

 

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
Helper I
Helper I

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

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors