topic Re: Calculate Pareto quickly in DAX Commands and Tips

Calculate Pareto quickly

Rai_BI — Mon, 20 May 2024 21:12:39 GMT

Hello friends, please can someone help me?
I need to create a DAX measure that calculates Pareto (80:20) of products. All the code I have written so far has resulted in failure as it exceeds available memory.

In my data model I have a product dimension table related to a sales fact table. The products table has 150 thousand rows and the sales table has 33 million rows. Download an example PBIX here.

I need a DAX measure that calculates the pareto of products, so I will be able to filter all products classified as "A" or "B" or "C". My attempts are taking 10 minutes or sometimes it doesn't load because it exceeds the available memory. I need a measurement or some technique that is quick to calculate.

Re: Calculate Pareto quickly

Anonymous — Tue, 21 May 2024 06:11:44 GMT

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.

Re: Calculate Pareto quickly

mark_endicott — Tue, 21 May 2024 09:51:38 GMT

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.

Re: Calculate Pareto quickly

Rai_BI — Tue, 21 May 2024 11:30:20 GMT

Hi! mark_endicott, thank you,

I wrote as you said, but take a look at the print below, my attempts exceeds available memory.

Re: Calculate Pareto quickly

mark_endicott — Tue, 21 May 2024 11:57:06 GMT

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.

Re: Calculate Pareto quickly

Rai_BI — Tue, 21 May 2024 12:24:39 GMT

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"

Re: Calculate Pareto quickly

chaoat — Wed, 22 May 2024 02:54:20 GMT

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Slow-problems-when-calculating-Pareto/td-p/3917595

这个回答非常棒，他的代码3和最后一个代码4，是有不同的。类似与v-tangjie-msft 反馈的。

Re: Calculate Pareto quickly

Anonymous — Wed, 22 May 2024 05:59:08 GMT

Hi Rai_BI ,

Measures cannot be placed on a slicer; you can create a calculated column as a slicer.

Paretto% = VAR vSales = [Sales Amount] VAR vTempTable = ADDCOLUMNS ( ALL( 'dProducts' ), "Sales", [Sales Amount] ) VAR vSalesTotal = SUMX ( vTempTable, [Sales] ) RETURN DIVIDE(SUMX ( FILTER ( vTempTable, [Sales] >= vSales ), [Sales] ) ,vSalesTotal,0)

Pareto Classification = VAR CumulativePercent = [Paretto%] RETURN SWITCH(TRUE(), CumulativePercent <= 0.8, "A", CumulativePercent <= 0.95, "B", "C" )

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.

Re: Calculate Pareto quickly

mark_endicott — Wed, 22 May 2024 11:01:30 GMT

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.

Re: Calculate Pareto quickly

Rai_BI — Thu, 23 May 2024 12:16:12 GMT

mark_endicott Thank you very much !