Hi, I am trying to group together specific categories in a dimension, to create another metric.
In the case below, Y=C+D.
In Excel, I would simply use SUMIFS to sum by Type and Week.
But I cannot seem to do this using a secondary table calculation, whilst the query has the original data.
Can anyone help?!?
| Type1 | Week | Qty | Type2 |
|---|---|---|---|
| A | 1 | 54 | Z |
| B | 1 | 26 | Z |
| C | 1 | 73 | Y |
| D | 1 | 83 | Y |
| A | 2 | 23 | Z |
| B | 2 | 8 | Z |
| C | 2 | 75 | Y |
| D | 2 | 12 | Y |
| A | 3 | 15 | Z |
| B | 3 | 12 | Z |
| C | 3 | 57 | Y |
| D | 3 | 93 | Y |
…into…
| Week | Total | Type2 | Logic | Y | %Y |
|---|---|---|---|---|---|
| 1 | 236 | Y | C+D | 156 | 66% |
| 2 | 118 | Y | C+D | 87 | 74% |
| 3 | 177 | Y | C+D | 150 | 85% |
plot graph of %Y vs weeks: