cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper IV

## Slow and infunctional Pareto calculation

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.

7 REPLIES 7
Super User

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

Helper IV

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.

Super User

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?

Helper IV

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.

Helper IV

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.

Super User

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

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