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.