Slow and infunctional Pareto calculation

[Rai_BI]

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.

[lbendlin]

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

[Rai_BI]

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.

[lbendlin]

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?

[Rai_BI]

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.

[Rai_BI]

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.

[lbendlin]

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

[lbendlin]

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