Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Rai_BI
Helper IV
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
lbendlin
Super User
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

 

lbendlin_0-1715811160396.png

lbendlin_1-1715811468706.png

 

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?

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.