Create PowerPivot reports summed at high level with granular calculations

[sposean]

Hello,Summary of my problem: Because of memory issues, I would like to create PowerPivot Reports that are summed at a high detail level, but the calculations are done at a granular detail level.


Here is a simplified version of the details of my problem:
-I am using the 32 bit version of Excel, and the 64 bit version is not an option to me
-I have 1.5MM rows of product sales data
-I have 2 detail levels:
-Detail level #A - Division - 20 categories
-Detail level #B - Product # - ~150K categories
I have 2 time periods:
-Time Period #T - Year=2014
-Time Period #T-1 - Year=2013


Using DAX, I have calculated a flag to determine if the product has comparable units in time period [#T] and time period [#T-1]. If the units are not comparable, the product is excluded from calculation.
This model works when I create PowerPivots at the product level. However, due to memory restrictions, I can only pull this information by filtering for the information in very small batches (~5K products at a time).
I would like to view the information at the Division level. But when I attempt to do so, the DAX Measure flag I created does not work as planned, because when all the products sales are rolled up to the Divisional level, they all pass my comparability test, and are all included.
I would like to view the information at the Divisional level, but I need my DAX Measure to perform all calculations at the Product level.


For some additional background, my flags are created as follows:


Receipt Quantity - RQ1: =CALCULATE(sum(Purchases[RCPT_QTY]),filter(Purchases,Purchases[Year - Month]>[Year Month - RQ1]))
Receipt Quantity - LRQ1: CALCULATE(sum(Purchases[RCPT_QTY]),filter(Purchases,Purchases[Year - Month]>[Year Month - LRQ1]),filter(Purchases,Purchases[Year - Month]<[Year Month - RQ4]))
Ratio - Receipt Quantity - RQ1: =[Receipt Quantity - RQ1]/[Receipt Quantity - LRQ1]
Flag - Receipt Quantity - RQ1: =if([Ratio - Receipt Quantity - RQ1]<10,if([Ratio - Receipt Quantity - RQ1]>.1,1,0),0)


Much Thanks in advance.

 

[sposean]

Alright, after much searching I think I have answered my own question, and the solution is far simpler than I was expecting. I am looking for the SUMX formula. I was not aware of its existence. Surprised no PowerPivot Pro could've helped me with this!

Here is a good explanation of the SUMX formula I found: SUMX() – The 5-point palm, exploding fxn technique « PowerPivotPro

Thanks anyways