Show values specific to one item on one table row

[MCosenza]

Hi Everyone,

I have a table where I track KPI data over a four week period and I have created a table to capture a weekly target, the actual value against the target and a status column where "0" is good and "1" is bad.

You'll see from the attachment that each KPI is shown 4 times to allow for each week's data. How can I reduce this to just show the four weeks of data on one line? I tried to recreate the table using DISTINCT and SELECTCOLUMNS but I kept running into the same issue. Any ideas are greatly appreciated. Thanks.

[mahenkj2]

You may upload the file on google drive or any such portal and share the link here.

[MCosenza]

Hopefully you can access it on my onedrive folder.

https://networkrail-my.sharepoint.com/:x:/g/personal/mcosenz1_networkrail_co_uk/ERJnetWwhz9JuD7Mhd7j... 

[mahenkj2]

You need to give access, it demands password.

[MCosenza]

Sorry about that, I've moved the example file to my Dropbox account so you should be able to view it from there. The link is: https://www.dropbox.com/sh/kfrofp1g6jonvbp/AAAbngMi8h6ProCObNHLgh6Aa?dl=0

[mahenkj2 · Accepted solution]

Hello @MCosenza ,

You need to transform your data to get better insights. Originally, you may have two sets of data, but infact they are related, target and actual and our traget is to bring actual and target data in one row, either physcially in one table or by relating two tables. What you are so far doing, is using the typical excel flat table but most of the times it is useful if your multiple columns can go in single column (unpivot) if they represent same attribute like target data all in one column.

I have tried to create a sample, in which in the KPI table, I have unpivot the KPI data and same way, in actual data table, again did unpivot of actual data. Based on the data you shared, it seems that each combination of KPI and period value is unique, so merged those two columns in both the data sets. This merged column shall become our link column to create a one to one relationship.

Now, it is easy to see target and actual data on one row:

Please find the sample file here:

https://drive.google.com/drive/folders/1BITxnzqNzQ6T62Gf9F8NzNu9BN2u6QN4?usp=sharing

[mahenkj2]

Can you also please paste your sample data here (removing sensitive data if any)?

[MCosenza]

I've stripped back the sensitive data and just added dummy data into the file so you can get an idea of the existing structure but I'm new to the forum so not sure how I can add the .xlsx file to the message.