Sumifs in PowerPivot

[Andrew_UK]

Hi All,

I've browsed around trying to find an answer to this and have found answers which I've been able to recreate and get working (situationally) for Sumif (singular condition). I need a sumif with two variable conditions rather than fixed.

Basically the two tables I have are like this. 1 shows new lines launched, with a launch date, the other shows sales. I need to measure the impact which sending out free samples has on the first three months of sales - should be easy :P

New Lines:

[Product ID] [Samples sent?] [Launch Month] [Second Month] [Third Month] [Sales in First Month] [Sales in Second Month] [Sales in Third Month]
eg 1234....... yes................... Jan 2015.......... Feb 2015........ Mar 2015.......... SUMIFS()............... SUMIFS().................... SUMIFS()
eg 2345....... No.................... Apr 2015.......... May 2015....... Jun 2015........... SUMIFS()............... SUMIFS().................... SUMIFS()


Sales Table

[Product ID] [Invoice Month] [Sales Value]
eg 1234......... Mar 2015......... 200


Now for normal excel I would do the equivalent of Sumifs(Sales.[Sales Value],Sales.[Invoice Month],[Launch Month],Sales.[Product ID],[Product ID])

I've tried playing around with filters but they don't seem to like having a variable condition, I've also tried playing around with the calculate function - but that doesn't seem to like having multiple filters... I'm kind of going round in circles here and can't find the answers I need on the forums here.

The final step will be easy, build a pivot table to compare first 3 months of sales for all products where samples were sent, vs all products where they weren't I can do that!!

Thanks in advance for your help. I love these forums and really enjoy learning more tips and tricks

 

[Andrew_UK]

I found a solution. It's funny how sometimes just stating these things out loud can help you to work things through. The solution which worked for me was to go into the Sales table and Create a new column with a big if in it

=IF(RELATED('New Lines'[Launch Month])=[Invoice Month],"First Month",IF(RELATED('New Lines'[Second Month])=[Invoice Month],"Second Month",IF(RELATED('New Lines'[Third Month])=[Invoice Month],"Third Month",BLANK())))

Now I can build a PivotTable with "Months After Launch" as a Column Label - we're good

I get the sense though that there's a neater way and if there is then I'd love to learn it.

 

[TimRodman]

Does your data already have multiple month columns for each row?

If not, can't you just drag [Product ID] to the rows section of the pivot table and [Month] to the column section of the pivot table?

 

[Andrew_UK]

Sadly I can't just drag month in like that. The reason being is that product launches happen throughout the year, so a product launched in January will have Month 1 as January, Month 2 as February, Month 3 as March - but something launched in April will have Month 1 as April, Month 2 as May, Month 3 as June. All products need to be monitored across their first three months of performance both for customers that receive samples and those that don't. This is all being done so we can measure the 3 month impact of sending out free samples.

 

[TimRodman]

If you were to add a Calculated Column to the Power Pivot table that returned "Month 1", "Month 2", "Month 3", and "Exclude Me" then you could drag that column onto the columns in the Pivot table and filter out "Exclude Me".