Grouping by Two Variables

[MacFletch]

I'm trying to create a calculated column that sums up revenue by two variables - UniqueID and Quarter. Then I need to apply a segment to the calculated column

[TABLE="width: 276"]
<tbody>[TR]
[TD]Unique ID[/TD]
[TD]Quarter[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2013 Q1[/TD]
[TD]4,983[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2013 Q2[/TD]
[TD]797[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2013 Q3[/TD]
[TD]6,445[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2013 Q4[/TD]
[TD]8,285[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2014 Q1[/TD]
[TD]1,292[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2014 Q2[/TD]
[TD]2,338[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2014 Q3[/TD]
[TD]9,602[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2014 Q4[/TD]
[TD]5,374[/TD]
[/TR]
</tbody>[/TABLE]

This is what the finished product should look like:

[TABLE="width: 644"]
<tbody>[TR]
[TD]Unique ID[/TD]
[TD]Quarter[/TD]
[TD]Sales[/TD]
[TD]Q2 '13 - Q1 '14 Sales[/TD]
[TD]Segment 1[/TD]
[TD]Q4 '13 to Q3 '14 Sales[/TD]
[TD]Segment 2[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2013 Q1[/TD]
[TD]4,983[/TD]
[TD]16,819[/TD]
[TD]B[/TD]
[TD]21,516[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2013 Q2[/TD]
[TD]797[/TD]
[TD]16,819[/TD]
[TD]B[/TD]
[TD]21,516[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2013 Q3[/TD]
[TD]6,445[/TD]
[TD]16,819[/TD]
[TD]B[/TD]
[TD]21,516[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2013 Q4[/TD]
[TD]8,285[/TD]
[TD]16,819[/TD]
[TD]B[/TD]
[TD]21,516[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2014 Q1[/TD]
[TD]1,292[/TD]
[TD]16,819[/TD]
[TD]B[/TD]
[TD]21,516[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2014 Q2[/TD]
[TD]2,338[/TD]
[TD]16,819[/TD]
[TD]B[/TD]
[TD]21,516[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2014 Q3[/TD]
[TD]9,602[/TD]
[TD]16,819[/TD]
[TD]B[/TD]
[TD]21,516[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]2014 Q4[/TD]
[TD]5,374[/TD]
[TD]16,819[/TD]
[TD]B[/TD]
[TD]21,516[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]

I have a linked table that applies the segment via this formula:
= CALCULATE (VALUES (ABCTable[KeyName]), FILTER (ABCTable, ABCTable[Start]<=[CustomerRevenue]&&ABCTable[End]>=[CustomerRevenue]))

I also have calculated fields that give me the sales for the two periods in questions, but when I try to filter the segment using a slicer it looks at total revenue.

Thanks in advance for any help!

MacFletch

 

[scottsen]

Gonna need more help on this one. Some more clarification of the wording/question... or a sample workbook or something.

(Though, this totally reads backwards to me: BCTable[Start]<=[CustomerRevenue])

 

[MacFletch]

scottsen,

Thanks for the reply. Sorry I wasn't clear enough. Hopefully this helps...

The objective is assign a segment (A, B, C, D, E, or F) to each UniqueID based on total sales during different periods of four consecutive quarters. In the example above, I have one UniqueID and sales over an eight-quarter period. I am trying to determine what segment this customer would be in for each period based on sales. Again, a period is four consecutive quarters. To illustrate, I would like to determine which segment this customer was in during the period covering 2013 Q2 through 2014 Q1 and compare to the segment the customer was in from 2013 Q4 to 2014 Q3.

I have a calculated field that I can use to sum sales by UniqueID by period.

I am trying to use a linked table that has buckets based on sales with each bucket being aligned to a segment. That's where CALCULATE formula comes in. It works well, but unfortunately it uses all sales for the UniqueID. I am looking for a way to further filter get sales only for the period I need.

For some reason, I'm not allowed to post attachments. Is there another way to get you a sample workbook?