Calculate columns which are identified on rows

[killingaxe]

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.

[v-piga-msft · Accepted solution]

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

[killingaxe]

Hi @v-piga-msft,

This worked perfectly.

Thanks.