Answered by:
Excel sumifs and the powerpivot version help please
Question

very new to power pivot, can you help please?
In an excel table I was able to do the following...
=SUMIFS([GWP],[Date],[@Date],[PolType],[@PolType])
I have 60k rows where the above totals all GWP where the date & policy type match the individual record. This in effect gives a subtotal, per date per policy type rather than doing it in a pivot table. This works nicely however it takes for ages to update, and calculate, also has issues when I then need to pivot table it.
As a result I'm trying to use powerpivot, as I understand it is better at dealing with these big calculations, I've tried the calculate formula, but I cannot understand how to have a rolling criteria in the field. Like I can in my sumifs.
Thank you in advance for your help
Mathew
Wednesday, February 12, 2014 2:41 PM
Answers

To duplicate your original SUMIF as a Calculated Column in PowerPivot you could use one of the following:
Using EARLIER():
=CALCULATE( SUM(Table1[GWP]), FILTER( Table1, Table1[Date] = EARLIER(Table1[Date]) && Table1[PolType] = EARLIER(Table1[PolType]) )
Using ALLEXCEPT:
=CALCULATE( SUM(Table1[GWP]), ALLEXCEPT( Table1, Table1[Date], Table1[PolType] )
)Both should produce the same result as a Calculated Column.
The second version with ALLEXCEPT() should also work a Measure/Calculated Field:
ALLEXCEPT Measure:= CALCULATE( SUM(Table1[GWP]), ALLEXCEPT( Table1, Table1[Date], Table1[PolType] ) )
The advantage of the measure is that it should return the expected result in a pivot and filtering dynamically.
Your final measure would then be:
Percent GWP:= SUM(Table1[GWP])/[ALLEXCEPT Measure]
In all examples, replace 'Table1' with your actual table name in Power Pivot.
 Edited by Mike DietterickEditor Thursday, February 13, 2014 4:05 PM
 Marked as answer by Stumpy101 Thursday, February 13, 2014 4:12 PM
Thursday, February 13, 2014 4:04 PMAnswerer
All replies

If I understand your question correctly, you can create a measure in PowerPivot e.g.
TotalGWP:= SUM([GWP]) assuming your column name is GWP
Then, in Pivot table, add policy type and date to row labels, the measure will be evaluated within the filter context and calculate TotalGWP for each policy/date.
Ayad
Wednesday, February 12, 2014 2:56 PM 
Thank you Ayad,
But I'm trying to avoid having a pivot do these subtotal.
Sorry I should state that data is in this format:
Date Company Poltype GWP
A data is repeated for all companies and all companies are reported for all policy types, to allow M.I. filtering.
As a result a pivot for all companies would work, but If I then state a company or list of companies in my filters then the totals are not correct, as I'm trying to show the % of GWP per scheme against the each policy type per month.
Wednesday, February 12, 2014 3:07 PM 
Hi Stumpy101,
Are you able to illustrate with an example and perhaps a screenshot (with anonymised data) so that the community can get a better picture of the exact behavior you are after?
Thanks,
Michael
 Edited by Michael Amadi Wednesday, February 12, 2014 3:34 PM
Wednesday, February 12, 2014 3:34 PM 
Hi Stumpy101,
Are you able to illustrate with an example and perhaps a screenshot (with anonymised data) so that the community can get a better picture of the exact behavior you are after?
Thanks,
Michael
Hi Michael,
Sorry the forum would not allow me to insert a picture (Arrr)
Date Company PolType GWP
Jan12 ABC M £1,500
Feb12 ABC M £2,500
The month rolls on for a year then, then the company is repeated if it has more data for another policy type, and the same year is repeated again, till all data for a company on all policy types are outputted. then is rolls on for the next year in the same manner.
The dates are repeated for each company code and then the company can appear again for another policy type code. can go on to Jan 14
My sumifs above worked but, when you have 60k records however it is slow, and for reasons above I cannot use a normal pivot table.
Thank you all for your time
Wednesday, February 12, 2014 4:43 PM 
You can use the CALCULATE with ALL functions to ignore the filter to sum up the total e.g.
GrandTotalGWP:= CALCULATE(SUM([GWP]), ALL(TableName)) or you can you use specific columns in ALL function to ignore them in filter context to get the total at certain level. Then use the TotalGWP/GrandTotalGWP to get the %.
Here is an example on how to use Calculate: http://sqlblog.com/blogs/marco_russo/archive/2010/01/03/howcalculateworksindax.aspx
 Edited by Ayad Shammout Wednesday, February 12, 2014 5:30 PM
Wednesday, February 12, 2014 5:25 PM 
To duplicate your original SUMIF as a Calculated Column in PowerPivot you could use one of the following:
Using EARLIER():
=CALCULATE( SUM(Table1[GWP]), FILTER( Table1, Table1[Date] = EARLIER(Table1[Date]) && Table1[PolType] = EARLIER(Table1[PolType]) )
Using ALLEXCEPT:
=CALCULATE( SUM(Table1[GWP]), ALLEXCEPT( Table1, Table1[Date], Table1[PolType] )
)Both should produce the same result as a Calculated Column.
The second version with ALLEXCEPT() should also work a Measure/Calculated Field:
ALLEXCEPT Measure:= CALCULATE( SUM(Table1[GWP]), ALLEXCEPT( Table1, Table1[Date], Table1[PolType] ) )
The advantage of the measure is that it should return the expected result in a pivot and filtering dynamically.
Your final measure would then be:
Percent GWP:= SUM(Table1[GWP])/[ALLEXCEPT Measure]
In all examples, replace 'Table1' with your actual table name in Power Pivot.
 Edited by Mike DietterickEditor Thursday, February 13, 2014 4:05 PM
 Marked as answer by Stumpy101 Thursday, February 13, 2014 4:12 PM
Thursday, February 13, 2014 4:04 PMAnswerer 
Thank you Mike, looking at this I believe you have answered my question 100% I'm still very fresh to these types of formulas. thank you for the nicely laid out formulasThursday, February 13, 2014 4:19 PM