cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## Calculate Pareto quickly

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.

1 ACCEPTED SOLUTION
Super User

@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 =
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 =
cumulative_prod_amount,
"paretopct", DIVIDE ( [Cumulative Amount], [Total amount] )
)
VAR ABC =
_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.

9 REPLIES 9
Super User

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

Super User

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

Helper IV

Hi! @mark_endicott, thank you,

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

New Member

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Slow-problems-when-calculating-Paret...

Community Support

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.

Helper IV

Hi @v-tangjie-msft , 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"

Super User

@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 =
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 =
cumulative_prod_amount,
"paretopct", DIVIDE ( [Cumulative Amount], [Total amount] )
)
VAR ABC =
_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.

Helper IV

@mark_endicott Thank you very much !

Community Support

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors