Forum Discussion

Rai_BI's avatar
Rai_BI
Helper IV
2 years ago
Solved

Calculate Pareto quickly

Hello friends, please can someone help me? I need to create a DAX measure that calculates Pareto (80:20) of products. All the code I have written so far has resulted in failure as it exceeds availab...
  • mark_endicott's avatar
    mark_endicott
    2 years ago

    Rai_BI - Now that I know you need a static analysis, your best solutuion will be to create a calculated table that does all the calculations in one.

     

    Below is the DAX to create this table, you can change the ABC variable to suit your classifications & remove any unecessary columns from SELECTCOLUMNS() to remove them from the table. 

     

    To input this DAX go to Modeling > New Table.

     

    VAR sales_by_prod =
    SUMMARIZE (
        fSales,
        dProducts[NAME_PRODUCT],
        "Prod Amount", [Sales Amount],
        "Total amount", CALCULATE ( [Sales Amount], ALLSELECTED ( dProducts[NAME_PRODUCT] ) )
    )
VAR cumulative_prod_amount =
    ADDCOLUMNS (
        sales_by_prod,
        "Cumulative Amount",
            VAR PrdAmt = [Prod Amount]
            VAR Cumulate_amt =
                FILTER ( sales_by_prod, [Prod Amount] >= PrdAmt )
            RETURN
                SUMX ( Cumulate_amt, [Prod Amount] )
    )
VAR _pareto =
    ADDCOLUMNS (
        cumulative_prod_amount,
        "paretopct", DIVIDE ( [Cumulative Amount], [Total amount] )
    )
VAR ABC =
    ADDCOLUMNS (
        _pareto,
        "Pareto Classification",
            SWITCH ( TRUE (), [paretopct] <= 0.8, "A", [paretopct] <= 0.95, "B", "C" )
    )
VAR Result =
    SELECTCOLUMNS (
        ABC,
        "Product Name", dProducts[NAME_PRODUCT],
        "Sales Amount", [Sales Amount],
        "Pareto Amount", [paretopct],
        "Pareto %", FORMAT ( [paretopct], "#0.00%" ),
        "Pareto Classification", [Pareto Classification]
    )
RETURN
    Result

     

    This will give you a new table for analysis. 

     

     

    If this works for you, please accept it as the solution. 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Rai_BI ,

 

Through my testing, using the DIVIDE function optimizes the performance of the measure a little bit. However, due to the sheer volume of your data, I recommend filtering areas of your data and creating multiple visual objects.

Paretto% = 
VAR vSales = [Sales Amount]
VAR vTempTable =
    ADDCOLUMNS ( ALLSELECTED( 'dProducts' ), "Sales", [Sales Amount] )
VAR vSalesTotal =
    SUMX ( vTempTable, [Sales] )
RETURN
    DIVIDE(SUMX ( FILTER ( vTempTable, [Sales] >= vSales ), [Sales] ) ,vSalesTotal,0)

Best Regards,

Neeko Tang

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

 

  • Rai_BI's avatar
    Rai_BI
    Helper IV
    2 years ago

    Hi Anonymous , than you for your help.
    When a filter a some products, in fact, the visual loads quickly, but how can I use this measure in a slicer for example? I was trying to create a calculated column so as i could use it in a slicer. I don´t use the filters panel.

    In slicer i need be able to filter Productos "A" or "B" or "C"

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Rai_BI ,

       

      Measures cannot be placed on a slicer; you can create a calculated column as a slicer.

       

      Paretto% = 
VAR vSales = [Sales Amount]
VAR vTempTable =
    ADDCOLUMNS ( ALL( 'dProducts' ), "Sales", [Sales Amount] )
VAR vSalesTotal =
    SUMX ( vTempTable, [Sales] )
RETURN
    DIVIDE(SUMX ( FILTER ( vTempTable, [Sales] >= vSales ), [Sales] ) ,vSalesTotal,0)

       

       

       

      Pareto Classification = 
VAR CumulativePercent = [Paretto%]
RETURN
SWITCH(TRUE(),
    CumulativePercent <= 0.8, "A",
    CumulativePercent <= 0.95, "B",
    "C"
)

       

       

       

      Best Regards,

      Neeko Tang

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

    • mark_endicott's avatar
      mark_endicott
      Super User
      2 years ago

      Rai_BI - Now that I know you need a static analysis, your best solutuion will be to create a calculated table that does all the calculations in one.

       

      Below is the DAX to create this table, you can change the ABC variable to suit your classifications & remove any unecessary columns from SELECTCOLUMNS() to remove them from the table. 

       

      To input this DAX go to Modeling > New Table.

       

      VAR sales_by_prod =
    SUMMARIZE (
        fSales,
        dProducts[NAME_PRODUCT],
        "Prod Amount", [Sales Amount],
        "Total amount", CALCULATE ( [Sales Amount], ALLSELECTED ( dProducts[NAME_PRODUCT] ) )
    )
VAR cumulative_prod_amount =
    ADDCOLUMNS (
        sales_by_prod,
        "Cumulative Amount",
            VAR PrdAmt = [Prod Amount]
            VAR Cumulate_amt =
                FILTER ( sales_by_prod, [Prod Amount] >= PrdAmt )
            RETURN
                SUMX ( Cumulate_amt, [Prod Amount] )
    )
VAR _pareto =
    ADDCOLUMNS (
        cumulative_prod_amount,
        "paretopct", DIVIDE ( [Cumulative Amount], [Total amount] )
    )
VAR ABC =
    ADDCOLUMNS (
        _pareto,
        "Pareto Classification",
            SWITCH ( TRUE (), [paretopct] <= 0.8, "A", [paretopct] <= 0.95, "B", "C" )
    )
VAR Result =
    SELECTCOLUMNS (
        ABC,
        "Product Name", dProducts[NAME_PRODUCT],
        "Sales Amount", [Sales Amount],
        "Pareto Amount", [paretopct],
        "Pareto %", FORMAT ( [paretopct], "#0.00%" ),
        "Pareto Classification", [Pareto Classification]
    )
RETURN
    Result

       

      This will give you a new table for analysis. 

       

       

      If this works for you, please accept it as the solution.