SUMIFS or SUMPRODUCT equivalent in PowerPivot

[L33]

Trying to learn PowerPivot and I promise I won't be on here constantly with every little thing, but if someone could give me a pointer on this one I'd be very grateful...

This is so basic in standard Excel that I'm annoyed with myself I can't work it out in PowerPivot.. I've got a table of 4 items each repeated for 3 months... I've got a blank field there and I want to add the values found in a second table to this field. In standard Excel this would be simple SUMPRODUCT or SUMIFS formula.. I've tried to get to grips with SUMX but I've no idea where to start with it. (I'm very new to this, and I have a suspicion that the real answer to this problem has to do with mental shift and thinking about the problem in a different way, rather than simply trying to replicate what I'd do in normal Excel, but baby steps....)

The "Sum of Resolution Time" here is what I want to fill in (and here I've shown how it would look)

	A	B	C
Serial	Month	Sum of ResolutionTime
V410835
V410835
V410835
V534111
V534111
V534111
V718374
V718374
V718374
V875849
V875849
V875849

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]Jan-14[/TD]
[TD="align: right"]13.61[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]Feb-14[/TD]
[TD="align: right"]16.63[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]Mar-14[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]Jan-14[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]Feb-14[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]Mar-14[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]Jan-14[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]Feb-14[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]Mar-14[/TD]
[TD="align: right"]18.5[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]Jan-14[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]Feb-14[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]Mar-14[/TD]
[TD="align: right"]2.6[/TD]

</tbody>

and I want to grab the values from the Resolution field here:

	G	H	I	J
Serial	Date	MonthYr	Resolution
V410835
V410835
V410835
V410835
V410835
V410835
V718374
V718374
V718374
V875849

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]09/01/2014[/TD]
[TD="align: right"]Jan-14[/TD]
[TD="align: right"]2.49[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]18/01/2014[/TD]
[TD="align: right"]Jan-14[/TD]
[TD="align: right"]9.49[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]27/01/2014[/TD]
[TD="align: right"]Jan-14[/TD]
[TD="align: right"]1.63[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]19/02/2014[/TD]
[TD="align: right"]Feb-14[/TD]
[TD="align: right"]9.05[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]27/02/2014[/TD]
[TD="align: right"]Feb-14[/TD]
[TD="align: right"]5.2[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]28/02/2014[/TD]
[TD="align: right"]Feb-14[/TD]
[TD="align: right"]2.38[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]01/03/2014[/TD]
[TD="align: right"]Mar-14[/TD]
[TD="align: right"]2.88[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]03/03/2014[/TD]
[TD="align: right"]Mar-14[/TD]
[TD="align: right"]9.59[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]05/03/2014[/TD]
[TD="align: right"]Mar-14[/TD]
[TD="align: right"]6.03[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]28/03/2014[/TD]
[TD="align: right"]Mar-14[/TD]
[TD="align: right"]2.6[/TD]

</tbody>

Many thanks in advance for the assistance...

 

[Tianbas]

Iam not sure if you really need a sumif or sumproduct for that. If you just use Serial and MonthYr in the rows of your pivot table and the sum of resolution in the value field you should see your target table

If you need something like a sumif the following formulas can help
as a calculated measure in a pivot table try =CALCULATE( SUM(TABLE[Resolution]),ALLEXCEPT(TABLE,TABLE[Serial]))
as a calculated column in the data model try =CALCULATE( SUM(Table[Resolution]),FILTER(ALL(Table), Table[Serial]=EARLIER(Table[Serial])))

a sumproduct is more difficult...

 

[L33]

Thanks for the response. Just to clarify, the first table isn't the final output, it is another table in my data model that I think I need to populate. I can't get your data model formula to work though...

To do what I'm trying to do in standard Excel is very simple:
I have a list of Devices, listed as many times as there are Months in my report period (in my sample, 4 devices for each one of 3 months (Jan-March) ).

Against each Device-Month row, I'd have a column that tells me the Potential Available hours - eg. 168 working hours in the month for it to potentially be required for.

Then I'd get the Downtime for the Device-Month pairing which by getting the sum of any Resolution times against that Device in that Month, retrieved from that other table (the Service table). Another column simply deducts "DownTime (aka sum of Resolution time)" from the "Potential Available" to give me the Uptime hours.

I need an output that displays per Month the Uptime % across the entire fleet of devices - or across whatever Slicer criteria is selected - ie. the sum of the Uptime amounts over the sum of the potential Available times.

I'm having trouble working out how I'd do all this in PowerPivot.

Very appreciative of any pointers.

 

[scottsen]

Hi L33 and welcome

I think it might be helpful if we could see your power pivot model, so we can see what your relationships look like.

I get the feeling you are "over thinking" this one If you had that second table, and created a pivot table with
serial and date on rows, the sum of resolution would show up just like you want, by just dropping it on values.

 

[L33]

I don't doubt I'm over-thinking it, and I'm probably not explaining myself very well. No worries - I'll keep reading my new book on the subject and I'm sure the solution will reveal itself to me.

Thanks anyway.

 

[scottsen]

You are making me glad that I am not an actual "excel pro", just Power Pivot I'm never bothered that "this is easy with SUMIF's" and the like

Your initial images are from sheets, if you gave us images of your power pivot model, that would help me and Tianbas get you going. His answer looks like what I would have expected and it's not clear to me what is not working there. Seeing the relationships between your tables would help us understand how the filters flow between them.

I do see some time elements (which suggests you will want a real and separate calendar table), and repeated serial #'s, which may suggest you will need a table where those values are unique (as dealing with a true many to many situation is a pain). But without seeing more, it is hard to help.