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.
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"
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.
mark_endicott Thank you very much !