Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Memory Issue For Pareto Calculation

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
Need Help

7 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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 ParentSupplier Vendor ChildSpend
      ABCABC 115423
      ABCABC 22345
      MAGNETSMagnets177.988767
      MAGNETSMagnets256767
      UNICORNUNICORN12459
      UNICORNUNICORN215236
      UNICORNUNICORN32133
      UnclassifiedUnclassified15423
      ATH AOATH AO2345
      Te Zheng XingTe Zheng Xing A77.988767
      Te Zheng XingTe Zheng Xing B56767
      Te Zheng XingTe Zheng Xing C2459
      Te Zheng XingTe Zheng Xing D15236
      Premier PressPremier Press12133
      Premier PressPremier Press11345

       

      Expected Result

       

      Vendor ParentCumulative SpendCumulative Spend %
      ABC1776817786/190226.9
      MAGNETS56844.956844.9/190226.9
      UNICORN19828 
      Unclassified15423 
      ATH AO2345 
      Te Zheng Xing74539.9 
      Premier Press3478 

       

      Hope I am able to explain it clearly.

       

      Many Thanks

      Regards

      Ankhi

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        7 years ago

        Anonymous I guess this is what you are looking for