Memory Issue For Pareto Calculation

Anonymous · ‎09-11-2019

Hi All,

I am trying to do a Pareto Calculation. My source of data is excel having row counts of 1,274,444. But the cumulative sum is not working due to a memory issue. I tried a couple of times but eventually after waiting for 15-20 mins it gives me an out of memory error.

My RAM(in VDI) is 6 GB . I have even tried increasing the Power BI Cache size to 6 GB from the default size. But it did not work. Any idea how this could be resolved?

Its a cumulative sum of spend for vendors. The DAX is below

Cumulative Spend = IF(HASONEVALUE('2017_2018'[Supplier - Vendor Global Ultimate Parent (enr)]),

SUMX(TOPN([Rank],ALL('2017_2018'[Supplier - Vendor Global Ultimate Parent (enr)]),

[Total Spend],DESC),[Total Spend]),BLANK())

Thanks in advance.

Regards

Ankhi

parry2k · ‎09-11-2019

@Anonymous based on 1.2 million rows it should work fine. If you can share sample data and expected result, will able to write a DAX for you. Looking at your DAX, not sure what you are trying to achieve.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous · ‎09-11-2019

Hi Parry,

Below is the sample data. The vendor parent column has duplicate data. As it has vendor childs. I want a cumulative spend of all the vendor parent. I have provided another table below of what is expected. And then finally a cumulative percentage.

I have given just 3 columns on which I want the calculations. However the excel has many other columns and the rowcount is almost 2M as mentioned in my previous post.

Sample Data:

Supplier - Vendor Parent	Supplier Vendor Child	Spend
ABC	ABC 1	15423
ABC	ABC 2	2345
MAGNETS	Magnets1	77.988767
MAGNETS	Magnets2	56767
UNICORN	UNICORN1	2459
UNICORN	UNICORN2	15236
UNICORN	UNICORN3	2133
Unclassified	Unclassified	15423
ATH AO	ATH AO	2345
Te Zheng Xing	Te Zheng Xing A	77.988767
Te Zheng Xing	Te Zheng Xing B	56767
Te Zheng Xing	Te Zheng Xing C	2459
Te Zheng Xing	Te Zheng Xing D	15236
Premier Press	Premier Press1	2133
Premier Press	Premier Press1	1345

Expected Result

Vendor Parent	Cumulative Spend	Cumulative Spend %
ABC	17768	17786/190226.9
MAGNETS	56844.9	56844.9/190226.9
UNICORN	19828
Unclassified	15423
ATH AO	2345
Te Zheng Xing	74539.9
Premier Press	3478

Hope I am able to explain it clearly.

Many Thanks

Regards

Ankhi

parry2k · ‎09-11-2019

@Anonymous I guess this is what you are looking for

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous · ‎09-11-2019

Hi Parry,

So sorry , my expected result is below. The cumulative spend is spend of vendor 1, then vendor 1 + vendor 2, vendor 1 + vendor2 + vendor 3 etc..

Vendor Parent	Spend	Cumulative Spend	Cumulative Spend %
ABC	17768	17768	17786/190226.9
MAGNETS	56844.9	74612	74612/190226.9
UNICORN	19828	94440	94440/190226.
Unclassified	15423	109863
ATH AO	2345	112208
Te Zheng Xing	74539.9	186748
Premier Press	3478	190220

parry2k · ‎09-11-2019

@Anonymous that is easy but do we want to keep the same sort order for parent?

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k · ‎09-11-2019

@Anonymous here is how it looks like if sorted by parent vendor

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous · ‎09-11-2019

when i pull it in the graph the vendors will be shown in descending order by Spend. I mean the vendor with the highest spend 1st and then decreasing. On x axis it will be vendor and i want to have the cumulative spend % as a line