Cumulative Total Performance

[Anonymous]

I have been trying to perform an 80/20 Analysis on product sales over the past 3 years.  This analysis ranks each product by its total sales if it is greater than 0.  I have two tables Products (200k rows 2 columns) and Sales (171k 6 columns).  I need to perform a cumulative total in order to compare the product sales amount to that cumulative total. 

My smaller businesses (<1k products) will return fine in about 8 minutes.  However, my two larger categories (70 k products each) will run for about 30 minutes.  This model is so scaled down to try to make it work since I thought having the full products and sales table was my issue.  However, I get the same performance with these greatly reduced column tables.  Plus, this isn't the endpoint.  I need to eventually show this in a quad chart and provide more information back to the user for analysis.

Is there any advice on how to make this perform more quickly?  I can't put these in the SQL queries because it will severely limit the flexibility of the analysis tool.  Thank you in advance!

Sales

Products

Test Inv Amount = sum(Sales[Amount])

Test Rank = RANKX(ALLSELECTED('Products'[Item Number]), [test inv amount], ,desc, Dense)

Cumulative test =

var OverallRanking = [Test Rank]

var Allitems = ALLSELECTED('Products'[Item Number])

var Rankitems = ADDCOLUMNS(Allitems, "Ranking", [Test Rank], "Inv Amt", [Test Inv Amount])

return

sumx( filter(Rankitems, [Ranking] <= OverallRanking),[Inv Amt])

[Anonymous · Accepted solution]

I believe I've landed on a solution where I can get accurate data.  This version of the 8020 analysis looks at the total sales and rates each product based on non-zero or non-negative sales.  I've used the performance analyzer to measure the times with each change.  I found using Calculatetable and Summarize in the bottom measure to perform the same.  It still takes > 220000 ms to respond but will return data locally and on the power bi cloud.

8020 Revenue =

calculate(

calculate([Invoice Amount], ALLSELECTED(Products[Item Number])),filter(Products,calculate([Invoice Amount]

, ALLSELECTED(Products[Item Number]))>0))

Cumulative 8020 Product Revenue =

VAR ProductRevenue = [8020 Revenue]

RETURN

calculate(sumx(

filter(

CALCULATETABLE(

addcolumns(ALLSELECTED(Products[Item Number]), "Product Revenue", [8020 Revenue])

,filter(addcolumns(ALLSELECTED(Products[Item Number]),

"Product Revenue", [8020 Revenue]),[Product Revenue] >0 && [Item Number] <> "unknown")),

[Product Revenue]>= ProductRevenue)

,[Product Revenue]))

[amitchandak]

@Anonymous , Please refer this blog from Matt, if that can help

https://exceleratorbi.com.au/pareto-analysis-in-power-bi/

[Anonymous]

So that helped, and I made some progress.  Instead of using the rankx function, it just uses the sales from a product and compares it against the total sales of the other products.  This knocked the processing time down from 22,378 ms to 126ms when using the performance analyzer.  Very impressive improvement, but still not enough for the most prominent categories.

I've changed the measure but still getting memory.  I've tried both the sumx and the calculate formulas. Neither can handle the most extensive product category, which only has 20k products (the other 50k don't have sales against them).  I've included the /Dax query from the performance analyzer on the category that will return.  If there is any more advice, I'd appreciate the help.  I'll work with the summarize and calculatetable to see if I can knock down the memory usage.  Many thanks for considering my request.

Cumulative Sales Amount =

VAR thisProductAmount = [Test Inv Amount] // this sets the sales amount for the current product

//var TotalAmountAllProducts = sumx(filter(allselected(Products), [Test Inv Amount] >= thisProductAmount ), [Test Inv Amount]) /* Total amount of sales greater than this product */

RETURN

//TotalAmountAllProducts

CALCULATE([Test Inv Amount], FILTER(ALLSELECTED(Products[Item Number]), [Test Inv Amount] >= thisProductAmount))

//) / TotalMarginAllProducts

// DAX Query

DEFINE

  VAR __DS0FilterTable =

    TREATAS({"Bianchi Duty Gear",

      "Bianchi"}, 'Products'[Product Subcategory])

  VAR __DS0Core =

    SUMMARIZECOLUMNS(

      ROLLUPADDISSUBTOTAL('Products'[Item Number], "IsGrandTotalRowTotal"),

      __DS0FilterTable,

      "Test_Rank", 'Sales'[Test Rank],

      "Cumulative_Sales_Amount", 'Sales'[Cumulative Sales Amount],

      "Test_Inv_Amount", 'Sales'[Test Inv Amount]

    )

  VAR __DS0PrimaryWindowed =

    TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, [Test_Rank], 1, 'Products'[Item Number], 1)

EVALUATE

  __DS0PrimaryWindowed

ORDER BY

  [IsGrandTotalRowTotal] DESC, [Test_Rank], 'Products'[Item Number]