Forum Discussion
Calculate columns which are identified on rows
Hi all,
I'm currently trying to setup a visual but am not able to manage.
I have got my data in one table, and the headers of this table match the rows of another table via which I would want to filter.
| Type | A | B | C | D |
| People | 1 | 2 | 3 | 3 |
| Machines | 10 | 6 | 8 | 2 |
| Terminators | 8 | 5 | 2 | 14 |
| Country | Sector |
| US | A |
| US | B |
| UK | C |
| CH | D |
So if I would filter on US an additional column would have the below result.
| Type | A | B | C | D | Total filterd (A+B) |
| People | 1 | 2 | 3 | 3 | 3 |
| Machines | 10 | 6 | 8 | 2 | 16 |
| Terminators | 8 | 5 | 2 | 14 | 13 |
Many thanks for looking into it.
Hi killingaxe,
For your output, you could get it without creating a calculated column.
Actually ,the calculated column is not dynamic so you can't get the output when you filter on US.
You could try to follow the steps below to get your desired output.
1. You should create the relationship of the two table so you need to unpivot columns in Query Editor.
2. Create the relationship of the two tables like below.
3.Then you could create the Matrix visual like below.
Hope it can help you!
Best Regards,
Cherry
2 Replies
v-piga-msftResident Rockstar
Hi killingaxe,
For your output, you could get it without creating a calculated column.
Actually ,the calculated column is not dynamic so you can't get the output when you filter on US.
You could try to follow the steps below to get your desired output.
1. You should create the relationship of the two table so you need to unpivot columns in Query Editor.
2. Create the relationship of the two tables like below.
3.Then you could create the Matrix visual like below.
Hope it can help you!
Best Regards,
Cherry