Another way to express this may be: the different groups each recipient has been assigned to. You can write a calculated field for this like:
However, because you don't need the number of groups but the number of changes, you can substract 1 (i.e. if a customer is only in one group, 0 should be displayed instead of 1):
[Group Changes] = COUNTD([Group]) - 1
If you drag that along Recipient to the Viz you'll get something similar to the workbook attached.
Groups_ss.twbx 11.9 KB
This wont be always true though, imagine
Recipient 1 moves from 3 to 5, then 5 to 3, then 3 to 8, then 8 to 3.
In this case total changes as 4, but according to your calculation I would get 2.
How do we solve this problem?
Well spotted! We can probably use a table calculation to help us [Current vs. Last]:
IF LOOKUP(ATTR([Group]), -1) <> LOOKUP(ATTR([Group]), 0)
LOOKUP takes a look at what's on the visualization. -1 looks at the prior value, 0 looks at the current (for this to work, Group and Date need to be on the Viz to make valid comparisons). With that, we can create diferent types of visualizations like the 'Bar Chart' sheet on the workbook attached. Make sure you have the table calculation setup like this:
You can follow a similar logic to get a total with thise table calculation: [Total Changes] = WINDOW_SUM([Current vs Last]). See examples on 'Crosstab + Filter' and 'Bar Chart + Filter ' sheets.
Groups.twbx 20.7 KB