Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cumulative Total Performance

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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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]))
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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]