Hi all,
I'm not entirely sure how to phrase this question, but here goes. I'll start with an example
Location Date Product Type
1 01/02/17 A
1 02/02/17 A
1 03/02/17 B
1 03/02/17 B
1 03/02/17 C
1 04/02/17 C
1 04/02/17 C
2 01/02/17 A
2 02/02/17 B
2 02/02/17 B
2 02/02/17 B
2 03/02/17 B
3 02/02/17 A
3 02/02/17 C
3 02/02/17 C
Lets say I want to add a new column that counts all the times that Product C has been sold in each location. I know I can do this with set analysis and it would give me
Location "C" Count
1 3
2 0
3 2
However, I would like this showing on every line when the table is fully expanded
Location Date Product Type "C" Count
1 01/02/17 A 3
1 02/02/17 A 3
1 03/02/17 B 3
1 03/02/17 B 3
1 03/02/17 C 3
1 04/02/17 C 3
1 04/02/17 C 3
2 01/02/17 A 0
2 02/02/17 B 0
2 02/02/17 B 0
2 02/02/17 B 0
2 03/02/17 B 0
3 02/02/17 A 2
3 02/02/17 C 2
3 02/02/17 C 2
I cant seem to get this to work becuase when I add the individual lines, it does the calcualtion line by line,not at the Location level.
I've tried a number of different combinations of
Sum(aggr(Count(Distinct {<[Product Type]={'C'}>} ProductKey),Location))
but none seem to give the desired results.
I appreciate any advice on this
Thanks
