Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Rai_BI
Helper IV
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

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

 

mark_endicott_0-1716375291683.png

 

If this works for you, please accept it as the solution. 

View solution in original post

9 REPLIES 9
mark_endicott
Super User
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. 

 

mark_endicott_0-1716292573050.png

 

mark_endicott
Super User
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. 

Hi! @mark_endicott, thank you,

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

Rai_BI_0-1716290912100.png

 

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

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

v-tangjie-msft
Community Support
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)

vtangjiemsft_0-1716271311117.png

vtangjiemsft_3-1716271803323.pngvtangjiemsft_2-1716271745112.png

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

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

 

mark_endicott_0-1716375291683.png

 

If this works for you, please accept it as the solution. 

@mark_endicott Thank you very much !

Hi @Rai_BI ,

 

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

vtangjiemsft_0-1716356684129.png

 

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

 

vtangjiemsft_1-1716357542439.png

 

 

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. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors