Fetching a summary of recurring values | Power Pivot | Excel Forum

OK, to update, I've worked out how to consolidate the dates in the second table, by modifying some of the values to a negative (based on specific rules) so the net sum of these values gives the actual time expired. What I cannot workout is how to get a sum lookup to work, so that when I go back tot eh master table I can sum all of the adjusted values for each master record. I've tried using a SUM calculation in the master Power Pivot table but it simply sums the whole column, not the specific values applicable to each master record. Anyone got any ideas at all?

Catalin,

The attached is pretty much the best I can do without breaching any confidentiality that applies. The real data and system are sensitive, so I cannot extract a 'real' sample, but I think the attached gives you the gist of the issue.

I need to calculate the sum of the time expired within the actions. I've sued the adjusted values to get to a positive value for on hold and negative value for off hold, with the aim of simply summarising the actions for each ticket. What I need in column E is the DAX (or normal formula for that matter) that looks up the ticket ID and summarises all action entries for that ticket.

I should point out that on the actions table, they are recorded in date order, though I can of course apply a custom sort in the Power Query if sort order is a factor.

Si

Oops, my bad.

Thanks Catalin, I didn't realise SUMPRODUCT would work that way. It's kind of working but producing wrong values, based on sample testing. I need to dig through the syntax to see where I'm going wrong. I suspect the issue lies with the nature of the data, not the formula.