Forum Discussion

Rai_BI's avatar
Rai_BI
Icon for Helper IV rankHelper IV
2 years ago
Solved

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 diff...
  • xifeng_L's avatar
    xifeng_L
    2 years ago

    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 = 
    ADDCOLUMNS ( 
        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~

     

xifeng_L's avatar
xifeng_L
Icon for Super User rankSuper User
2 years ago

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~

 

 

 

Rai_BI's avatar
Rai_BI
Icon for Helper IV rankHelper IV
2 years ago

Hi xifeng_L 
Is not working. It continues to take a long time to load.
Here is an example of the pbix file. Download

  • xifeng_L's avatar
    xifeng_L
    Icon for Super User rankSuper User
    2 years ago

    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 = 
    ADDCOLUMNS ( 
        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~

     

    • Rai_BI's avatar
      Rai_BI
      Icon for Helper IV rankHelper IV
      2 years ago

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

      • xifeng_L's avatar
        xifeng_L
        Icon for Super User rankSuper User
        2 years ago

        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.