locked
Excel sumifs and the powerpivot version help please RRS feed

  • 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.

    Thursday, February 13, 2014 4:04 PM
    Answerer

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


    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

    Jan-12  ABC            M            £1,500

    Feb-12  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/how-calculate-works-in-dax.aspx

    Ayad


    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.

    Thursday, February 13, 2014 4:04 PM
    Answerer
  • 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 formulas
    Thursday, February 13, 2014 4:19 PM