I love this problem, been turning it over in my head for a few hours now. But I've concluded that PowerPivot isn't a big help for this particular problem. PowerPivot itself is not a good "data shaper" and what you need here is more granularity in your rows of data - you need three rows for John where you currently only have 1. PowerPivot is an aggregator - it helps you turn more rows into less, not vice versa. Just like normal pivots, but with lots more benefits.