Forum Discussion
Calculate Pareto quickly
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.
Rai_BI - This is the measure I always use to create a pareto %
Total Qty Pareto by Product Type =
VAR total_qty =
CALCULATE ( [Total Qty], ALLSELECTED ( 'Product'[Product type] ) )
VAR pareto =
SUMX (
WINDOW (
0,
ABS,
0,
REL,
ALLSELECTED ( 'Product'[Product type] ),
ORDERBY ( [Total Qty], DESC )
),
[Total Qty]
)
RETURN
DIVIDE ( pareto, total_qty, 0 )
It is usually fairly optimal. If this does not work for you, perhaps you could try a Visual Calculation, as this will only calculate for the data within your visual. It is however in preview, so there are some limitations to them.
If my measure works, I'd be grateful if you could 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 反馈的。
