Create a new column that will calculate value from current table with result from other table

[StevenSeagull]

Hi community,

iam looking for a solution to my problem which I cant solve myself.

I have a two tables:

1st. Feature table, where I keep a chunk of work estimated on some value

2nd. User story table where I break down teh feature in to smaller piece called a User story.

Both are related with child parent relation.

Feature table								User story table
		Relation value						Relation value
Team name	Status	Feature ID	Tshirt size	New Tshirtsize				Parent feature ID	Team name	Status	Story points
Palantir	Active	10000	20	10				10000	Palantir	Closed	5	50%
Palantir	Active	20000	30	20				10000	Palantir	Closed	5
Palantir	New	30000	50	50				10000	Palantir	Open	10
Alpha	Active	40000	80	40				20000	Palantir	Closed	5	66,60%
Alpha	New	50000	40	40				20000	Palantir	Open	5
Alpha	New	60000	10	10				20000	Palantir	Open	5
								30000	Palantir	New	8	100%
								40000	Alpha	Closed	10	50%
								40000	Alpha	Open	5
								40000	Alpha	Open	5
								50000	Alpha	New	5	100%
								60000	Alpha	New	8	100%

I wnat to see create a new column calculation for Tshirt size which takes in to considertion the total user story completed. 

Formula should be: If Feature is in Active state find the feature ID in user story table (Parent feature ID) and sum up the open storypoints and devided with closed storypoints.

Example: Feature ID for Palantir 10000 has 3 User stories. Total value of User stories are 20. 10 are closed. That means 50% of Tshirt size 20 become 10

[rajendraongole1 · Accepted solution]

Hi @StevenSeagull  - Please find the attached pbix file FYR:

check the new column that derived for New Tshirtsize

Hope this helps .

[StevenSeagull]

I had to change 0 to 0.1. now it works.. thanks

[StevenSeagull]

Hi   rajendraongole1,

it works for some features, but there are some features where it doesnt work.. i did some research and it looks like those without closed stories doesnt show anz number in new tshirt size

[StevenSeagull]

Thanks  rajendraongole1,

it works but partialy. I noticed that If in active feature the user stories arent closed then it wont give any value