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 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.
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"