hi Marcus,

So one way to aggregate the data at the level you want, while still retaining the "Row Level" computation of your bands, is to use an FIXED LoD

I've changed the UnschBreak Mins calculation to

[UnschBreak Mins]

IF [Reason Code]="UnscheduledEventsBreak" THEN {FIXED [Reason Code], [Date],[W2K]: SUM([Mins])} END

So the Mins are summed across Reason Code, Date and W2K (ignoring Team)...the "Cross Tab - Example" tab on the attached shows what this is doing. This then brings this element into that bucket. The issue now is that as you have equated it at row level, returning a 1 which is then SUMed...this SUMs to 2 and not 1 (as both rows, from each team for that person, contain a 1...as both rows qualify).

One way round this, is to not return a 1, but the Person (or whatever it is you want to count), else NULL, and then use a COUNTD on that field (NULLs don't get counted in a COUNTD, like 0 don't get added in a SUM)

Hope that helps and makes sense.

No problem

So the way, I think to get around this is to use COUNTD, and not 1...

I've assumed here you are trying to count W2K's (if you're not, simply substitute where I used this with the field you want to be a unique count)

In your band calculations change the 1 returned for [W2K]

[Exceeded 5-10mins (Unsch)]

IF [UnschBreak Mins]>35 and [UnschBreak Mins]<40

THEN

[W2K]

END

and then change your measure value aggregations to COUNTD

You are then going to need to change some of your other calculations, to use COUNTD where these fields are referenced as aggregates.

A cheekier way might be to use another LoD, to count the number of non-required rows are with each W2K

IF [Reason Code]="UnscheduledEventsBreak" THEN {FIXED [Reason Code], [Date],[W2K]: COUNT([team])} END

or something like that...and then use this as a divider on the SUM fields, so in the example you posted it would be 2/2=1...and all other dates would be 1/1=1.

The real answer...is to go back and reshape your data so you have it at the grain you want to use it at. I often start with everything in...and then as I develop I go back to the data and re-aggregate to the level I need (eg. if I'm looking at sales by day, I don't need the hourly transactions). I may end up with multiple datasources at different grains, for different calcs/vizes, but use actions to blend/filter across their common dimensions (on a dashboard)...a bit of work, but time you get back 100 fold, in the long run (and makes Tableau that bit more fun!)