Forum Discussion
Calculate Pareto quickly
- 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 ResultThis will give you a new table for analysis.
If this works for you, please accept it as the solution.
Hi! mark_endicott, thank you,
I wrote as you said, but take a look at the print below, my attempts exceeds available memory.
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Slow-problems-when-calculating-Pareto/td-p/3917595
这个回答非常棒,他的代码3和最后一个代码4,是有不同的。类似与v-tangjie-msft 反馈的。