Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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.
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
@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.
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 |
@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.
@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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |