My table where I have the data is like this:
| Product ID | Category | Aisle movements | Flag |
| 1 | Carrot | Before movements | 1 |
| 1 | Carrot | To left | 1 |
| 1 | Carrot | Removed | 0 |
| 1 | Carrot | From left | -1 |
| 2 | Chilli | Before movements | 1 |
| 2 | Chilli | Removed | -1 |
I simply would like to make a matrix that would take Product Category as rows, Aisle movement as columns and sum of flag as matrix. Then I would like to calculate (sum of flags that are < 0) / (sum of before movements) to see how much % of the products have moved away from their previous position. I would want this as 1 single column added at the end of the matrix:
| Product Category | Before movements | To left | From left | Removed | Total | Moved % |
| Carrot | 1 | 1 | -1 | 0 | 1 | =-1/1 = -100% |
| Chilli | 1 | 0 | 0 | -1 | 0 | = -1/ 1 = -100% |
I have written a measure for this:
Moved % = calculate(sum(tbl[Flag]), tbl[Flag] < 0) / CALCULATE(SUM(tbl[Flag]), tbl[Aisle movements] = "Before movements")
But when I added this to the matrix, it also shows as other columns instead of one single column at the end. How do I switch those columns off?
Thank you
