Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
@Anonymous , Please refer this blog from Matt, if that can help
https://exceleratorbi.com.au/pareto-analysis-in-power-bi/
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.
// 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]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |