Forum Discussion
Calculated column - cannot get filtering to apply as needed
Hello All,
I have a table showing teams and their position in a hierarchy, store -> department -> team as follows:
| Store | Department | Team | ||
| A | W | 1 | ||
| A | W | 2 | ||
| A | X | 3 | ||
| A | X | 4 | ||
| A | X | 5 | ||
| B | Y | 6 | ||
| B | Z | 7 | ||
| B | Z | 8 | ||
| B | Z | 9 | ||
| B | Z | 10 |
I am trying to generate random hex colours at a department level so my desired result is something like this, where each department is assigned a colour:
| Store | Department | Team | Colour | |||
| A | W | 1 | "#B7E70C" | |||
| A | W | 2 | "#B7E70C" | |||
| A | X | 3 | "#52689E" | |||
| A | X | 4 | "#52689E" | |||
| A | X | 5 | "#52689E" | |||
| B | Y | 6 | "#F8EF1D" | |||
| B | Z | 7 | "#C286C7" | |||
| B | Z | 8 | "#C286C7" | |||
| B | Z | 9 | "#C286C7" | |||
| B | Z | 10 | "#C286C7" |
My formula for generating the colour is as follows:
"""#" &
MID("0123456789ABCDEF", 1 + ROUNDDOWN(RAND() * 16, 0), 1) &
MID("0123456789ABCDEF", 1 + ROUNDDOWN(RAND() * 16, 0), 1) &
MID("0123456789ABCDEF", 1 + ROUNDDOWN(RAND() * 16, 0), 1) &
MID("0123456789ABCDEF", 1 + ROUNDDOWN(RAND() * 16, 0), 1) &
MID("0123456789ABCDEF", 1 + ROUNDDOWN(RAND() * 16, 0), 1) &
MID("0123456789ABCDEF", 1 + ROUNDDOWN(RAND() * 16, 0), 1) & """
Inputting this alone gives me random colours at team level, therefore for the above data I get 10 different colours rather than the desired 4.
I have tried:
CALCULATE(...above colour logic..., ALLEXCEPT(DIM STORE, DIM STORE[Department])
CALCULATE(...above colour logic..., DIM STORE[Department] = EARLIER(DIM STORE[Department])
Both of which just return 1 distinct colour applied to all teams. Any ideas on how to get the desired result would be greatly appreciated.
1 Reply
Create a calculated table that summarizes over Store and Department (via a composite key, if you want) and then assign the random value to that. Join the calcuated table back to your original one via that key, or use TREATAS for the lookup.
