Indexing & SUMIF based on multiple criteria

[laurab92]

I have invoice data I get daily (sample attached), and it's listed by line item per invoice - so there's a line per product (sometimes multiple of the same product - due to a different purchase type, but they are grouped at this level by a distinct reference, the discounts are not), then any discounts applied in the rows below, with sometimes more than 1 discount for a product.  The discounts below each product always apply to the product above.

I currently do the following in excel (formulas also in the attached sample) but it requires a manual paste & extension each morning which isn't ideal.  The grey columns are something I feel comfortable replicating in PowerQuery already, so it's just the 2 columns on the RHS I am struggling with.

 

I'm looking to have a column set up within the PowerQuery stage that replicates COL L - so indexes each row that is relevant to the product as a repeated index, so I can then apply a calculation similar to COL M that gives a total £ paid for the product (with discounts deducted) against just the product line, and then display the value against discount rows as 0.

I have managed to create an Index in PowerQuery based just on the invoice #, but haven't been able to add in the 2nd constraint of the product level of detail, and also it removes all the other columns - just shows me the inv# column & the index!

Would be grateful for any suggestions on how best to solve this.

[ryan_mayu · Accepted solution]

@laurab92 

you can create an index column in PQ then use DAX to create two columns

Column = countx(FILTER(Sheet1,Sheet1[Index]<=EARLIER(Sheet1[Index])&&Sheet1[UnitPrice]>0&&Sheet1[Invoice Num]=EARLIER(Sheet1[Invoice Num])),Sheet1[UnitPrice])

Column 2 = if(Sheet1[TOTAL REVENUE FORMULA]>0,SUMX(FILTER(Sheet1,Sheet1[Invoice Num]=EARLIER(Sheet1[Invoice Num])&&Sheet1[Column]=EARLIER(Sheet1[Column])),Sheet1[TOTAL REVENUE FORMULA]),0)

please see the attachment below

[laurab92]

Thank you so much @ryan_mayu - your attachment looks perfect, sadly I think my data set is too big as it says there's not enough memory to perform the 1st index calc!

[v-deddai1-msft]

Hi @laurab92 ,

Please try to extend your memory on your pc. Restart if you get problems and make sure your memory has been release which can take a while. Close other memory hogs like browsers.

Remove the unused column and try to uncheck the option in the screenshot below:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai