topic Re: Slow and infunctional Pareto calculation in DAX Commands and Tips

Slow and infunctional Pareto calculation

Rai_BI — Wed, 15 May 2024 16:14:49 GMT

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.

Re: Slow and infunctional Pareto calculation

lbendlin — Wed, 15 May 2024 22:17:54 GMT

Your dimension table is too big. Power BI will have performance issues when you have more than 50000 distinct values in your dimension key.

It works fine when you use Product Brand, for example

Re: Slow and infunctional Pareto calculation

Rai_BI — Wed, 15 May 2024 23:28:58 GMT

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.

Re: Slow and infunctional Pareto calculation

lbendlin — Wed, 15 May 2024 23:49:01 GMT

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?

Re: Slow and infunctional Pareto calculation

Rai_BI — Thu, 16 May 2024 11:38:19 GMT

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.

Re: Slow and infunctional Pareto calculation

lbendlin — Thu, 16 May 2024 12:23:21 GMT

No user will ever want to scroll through 50000 rows in a table visual.

Re: Slow and infunctional Pareto calculation

Rai_BI — Fri, 17 May 2024 00:42:07 GMT

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.

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.

I need a code that doesn't take more than 10 seconds to load.

Re: Slow and infunctional Pareto calculation

lbendlin — Fri, 17 May 2024 01:31:25 GMT

You already posted that here Re: Slow and infunctional Pareto calculation - Microsoft Fabric Community - what is different this time?