cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Slow problems when calculating Pareto

Hello everybody,

I need to calculate the Pareto of the products, but I am unable to do so, PowerBI desktop is exceeding the available memory. I've already tried doing the calculation in several different ways, using variables or not using variables, using calculated columns, etc. But I'm failing.

One of the DAX measurements I made was the measurement below that is not working.

``````Paretto% =
VAR vSales = [Sales US\$]
VAR vSalesTotal = CALCULATE([Sales US\$], ALL('dProducts'))
RETURN
CALCULATE(
[Sales US\$],
FILTER(
ALL('dProducts'),
[Sales US\$] >= vSales
)
)/vSalesTotal``````

I've already done research on blogs and even here on the forum, but no solution helped me. Does anyone have a working DAX measure to calculate Pareto?

My data model is very simple, I have a product table related to a sales table. The products table has 200 thousand rows and the sales table has 33 million rows

1 ACCEPTED SOLUTION
Super User

Hi @Rai_BI ,

Pls try the following measure, which has the best performance and takes ablout 90 seconds.

``````Paretto% - optimized2 =
VAR vIndex =
ROWNUMBER (
ALL ( 'dProducts'[NAME_PRODUCT],'dProducts'[ID_PRODUCT] ),
ORDERBY ( [Sales Amount], DESC )
)
VAR vSales = [Sales Amount]
VAR vTempTable =
ALL ( 'dProducts'[NAME_PRODUCT],'dProducts'[ID_PRODUCT] ),
"Sales", [Sales Amount]
)
VAR vSalesTotal =
SUMX ( vTempTable, [Sales] )
RETURN
SUMX (
WINDOW ( 0, ABS, vIndex, ABS, vTempTable, ORDERBY ( [Sales], DESC ) ),
[Sales]
) / vSalesTotal``````

The other optimised measure takes about ten minutes.

From your original measure that would exceed the memory, first optimising to about 10 minutes, then continuing to optimise to 90 seconds, it feels like the limit of the optimisation has been reached, and if 90 seconds doesn't satisfy the demand, there's nothing I can do about it.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Microsoft MVP of PowerBI

5 REPLIES 5
Super User

Hi @Rai_BI ,

I've optimized the metric for you in two ways:

#1:

``````Paretto% =
VAR vSales = [Sales US\$]
VAR vTempTable =
ADDCOLUMNS ( ALL ( 'dProducts' ), "Sales", [Sales US\$] )
VAR vSalesTotal =
SUMX ( vTempTable, [Sales] )
RETURN
SUMX ( FILTER ( vTempTable, [Sales] >= vSales ), [Sales] ) / vSalesTotal``````

#2: Since it is not clear what your calculate environment is, it is possible that this method will not work

``````Paretto% =
VAR vIndex =
ROWNUMBER (
ALLSELECTED ( 'dProducts'[Product Name] ),
ORDERBY ( [Sales US\$], DESC )
)
VAR vSales = [Sales US\$]
VAR vTempTable =
CALCULATETABLE (
ADDCOLUMNS ( VALUES ( 'dProducts'[Product Name] ), "Sales", [Sales US\$] ),
ALLSELECTED ()
)
VAR vSalesTotal =
SUMX ( vTempTable, [Sales] )
RETURN
SUMX (
WINDOW ( 0, ABS, vIndex, ABS, vTempTable, ORDERBY ( [Sales], DESC ) ),
[Sales]
) / vSalesTotal``````

In addition to optimizing the measure, we can also increase the memory used for queries by simply changing the Query Limit Simulation option to Unlimited in the settings. Such as:

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Microsoft MVP of PowerBI

Helper IV

Hi @xifeng_L
Is not working. It continues to take a long time to load.

Super User

Hi @Rai_BI ,

Pls try the following measure, which has the best performance and takes ablout 90 seconds.

``````Paretto% - optimized2 =
VAR vIndex =
ROWNUMBER (
ALL ( 'dProducts'[NAME_PRODUCT],'dProducts'[ID_PRODUCT] ),
ORDERBY ( [Sales Amount], DESC )
)
VAR vSales = [Sales Amount]
VAR vTempTable =
ALL ( 'dProducts'[NAME_PRODUCT],'dProducts'[ID_PRODUCT] ),
"Sales", [Sales Amount]
)
VAR vSalesTotal =
SUMX ( vTempTable, [Sales] )
RETURN
SUMX (
WINDOW ( 0, ABS, vIndex, ABS, vTempTable, ORDERBY ( [Sales], DESC ) ),
[Sales]
) / vSalesTotal``````

The other optimised measure takes about ten minutes.

From your original measure that would exceed the memory, first optimising to about 10 minutes, then continuing to optimise to 90 seconds, it feels like the limit of the optimisation has been reached, and if 90 seconds doesn't satisfy the demand, there's nothing I can do about it.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Microsoft MVP of PowerBI

Helper IV

Thank you, but 10 minutes is too long. I need to found another way that load in maximum 10 seconds

Super User

The best performance can be up to 90 seconds, not 10 minutes. This is the limit of optimization, if you don't find any other solution, consider giving me the Solution! Thanks.

Microsoft MVP of PowerBI

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.