Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello friends,
I need help creating a DAX measure that calculates Pareto (80:20). All the measures I have taken so far have resulted in failure, as it is taking a long time to load and sometimes it does not load, as it exceeds the available memory.
My data model is very simple, I have a product dimension table related to a sales fact table. The products table has 200 thousand rows and the sales table has 33 million rows. Download a PBIX example here.
Please, does anyone know a method to perform this calculation so that it doesn't take too long to load the visuals?
Below is an example of code I wrote. But it takes a long time to load.
Cumulative Pareto =
Var vShare = DIVIDE(SUM('fSales'[Sales Amount]), CALCULATE(SUM('fSales'[Sales Amount]), ALL('dProducts')))
VAR TbShare =
SUMMARIZE(
ALLSELECTED('dProducts'),
'dProducts'[Cod Prod],
"@Share", DIVIDE(SUM('fSales'[Sales Amount]), CALCULATE(SUM('fSales'[Sales Amount]), ALL('dProducts')))
)
Return
IF(
HASONEVALUE('dProducts'[Cód Prod]) && [Share%] <> 0,
SUMX(FILTER(TbShare, [@Share]>=vShare), [@Share]),BLANK())
I need a code that doesn't take more than 10 seconds to load.
Yes, you are correct, but I still need to find a way to achieve this goal. There must be some technique or trick that makes this possible. There are several companies that have more than 50 thousand products, I refuse to believe that Power BI is incapable of performing this type of calculation for companies that have several products.
Look at it from the business side. That large number of products can never be shown all at once (your screen has a limited number of horizontal pixels too), and a pareto over these product may just show a straight line like in the example I gave.
What is the story you are trying to tell? What are the business insights you are hoping to get out of this exercise?
I don´t intend to use the measure in a graph like you did. I intend to use a slicer to filter products are A, B or C. I don´t intent to use a graph but i´ll use a table visual.
Hello,
I need help creating a DAX measure that calculates Pareto (80:20). All the measures I have taken so far have resulted in failure, as it is taking a long time to load and sometimes it does not load, as it exceeds the available memory.
My data model is very simple, I have a product dimension table related to a sales fact table. The products table has 200 thousand rows and the sales table has 33 million rows. Download a PBIX example here.
Please, does anyone know a method to perform this calculation so that it doesn't take too long to load the visuals?
Below is an example of code I wrote. But it takes a long time to load.
Cumulative Pareto =
Var vShare = DIVIDE(SUM('fSales'[Sales Amount]), CALCULATE(SUM('fSales'[Sales Amount]), ALL('dProducts')))
VAR TbShare =
SUMMARIZE(
ALLSELECTED('dProducts'),
'dProducts'[Cod Prod],
"@Share", DIVIDE(SUM('fSales'[Sales Amount]), CALCULATE(SUM('fSales'[Sales Amount]), ALL('dProducts')))
)
Return
IF(
HASONEVALUE('dProducts'[Cód Prod]) && [Share%] <> 0,
SUMX(FILTER(TbShare, [@Share]>=vShare), [@Share]),BLANK())
I need a code that doesn't take more than 10 seconds to load.
You already posted that here Re: Slow and infunctional Pareto calculation - Microsoft Fabric Community - what is different this time?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
30 | |
27 | |
24 | |
22 |