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.
Rai_BI - That is a shame, I have also tried a Visual Calculation using your sample file and the DAX below:
Pareto =
VAR _cumulative = SUMX( WINDOW( 1, ABS, 0, REL, ROWS, ORDERBY( [Sales Amount], DESC )), [Sales Amount])
VAR _total = COLLAPSEALL( [Sales Amount], ROWS )
VAR _pareto = DIVIDE( _cumulative, _total )
RETURN
_pareto
// FORMAT( _pareto, "#0,0.0%")
However again this exceeds the resources when unfiltered. You will need to filter your visuals so this does not use all +50,000 of your products.
The Visual Calculation will have the fastest response, but you will not be able to format the result as a % (yet, still in preview) without using FORMAT. However, when you do this it will turn the result into a string and therefore cannot be ordered.