Subtraction of different rows and columns

[Rene83A1]

Hi,

I have a table with date, team, cluster, KPI and the values.

I would like to subtract the values from column KPI 3 with column KPI 2, but the values should be assigned to the respective date, team and cluster.

Date	Team	Cluster	KPI	KPI 1	KPI 2	KPI 3	KPI 4	KPI 5	KPI 6
2022_01	Team1	Cluster1	KPI 1	1412
2022_01	Team1	Cluster1	KPI 2		171
2022_01	Team1	Cluster1	KPI 3			209
2022_01	Team1	Cluster1	KPI 4				0,851983
2022_01	Team1	Cluster1	KPI 5					0,12110482
2022_01	Team1	Cluster1	KPI 6						0,148017
2022_01	Team2	Cluster1	KPI 1	1412
2022_01	Team2	Cluster1	KPI 2		171
2022_01	Team2	Cluster1	KPI 3			209
2022_01	Team2	Cluster1	KPI 4				0,851983
2022_01	Team2	Cluster1	KPI 5					0,12110482
2022_01	Team2	Cluster1	KPI 6						0,148017
2022_01	Team1	Cluster2	KPI 1	1412
2022_01	Team1	Cluster2	KPI 2		171
2022_01	Team1	Cluster2	KPI 3			209
2022_01	Team1	Cluster2	KPI 4				0,851983
2022_01	Team1	Cluster2	KPI 5					0,12110482
2022_01	Team1	Cluster2	KPI 6						0,148017
2022_01	Team2	Cluster2	KPI 1	1412
2022_01	Team2	Cluster2	KPI 2		171
2022_01	Team2	Cluster2	KPI 3			209
2022_01	Team2	Cluster2	KPI 4				0,851983
2022_01	Team2	Cluster2	KPI 5					0,12110482
2022_01	Team2	Cluster2	KPI 6						0,148017

The result should be a new KPI

2022_01

Team1

Cluster1

KPI NEW

38

Thank you beforehand.

[Yossarian · Accepted solution]

Hi,

It seems tricky to organize the table as you want: you would need to have aditional rows to your original table with "KPI new" label. You coud do this:

CALCULATE([meas kpi 3] - [meas kpi 2], ALL(Table[KPI]))

This will give you the answer for all the rows in table but it won't show as additional lines. Moreover you'd need to work with some if statement to show the result only on the lines you'd want.

[Yossarian]

Hi,

Why do you have KPI rows and KPI columns?
If you just unpivot your data first, maybe a simple matrix visual will do the trick...

Hope it helps.

[Rene83A1]

Hi,

the original table looks like this

VK Team	Cluster	KPI	2022_36	2022_35	2022_34
Team1	Cluster1	KPI1	10	20	25
Team1	Cluster1	KPI2	50	5	52
Team1	Cluster1	KPI3	15	25	19
Team1	Cluster1	KPI4	100,0%	100,0%	100,0%
Team1	Cluster1	KPI5	0,00%	0,00%	0,00%
Team1	Cluster1	KPI6	0,00%	0,00%	0,00%