Calculate based on other columns

[Peavey]

Hello,

I have this similar dataset: 

Id	Task	Progress	Id Main task
1	Main task 1	?
2	Sub task 1	55	1
3	Sub task 2	20	1
4	Main task 2	?
5	Sub task 1	10	4
6	Sub task 2	0	4
7	Sub task 3	95	4
8	Main task 3	?
9	Sub task 1	50	8

Intention is to find the average progress based on the sub tasks in each main task. 

My start is: IF(Id main task = BLANK(), ???    Then I need to select all rows with id main task = Id and average these. 

How to?

Thanks

[Peavey]

Thanks for all the help, I found @vojtechsima solution to fit my needs very well!

This community is so impressive, so much knowledge and helpful people here, many thanks again!

-A-

[vojtechsima]

@Peavey  Happy to help, kudos appreciated. Thank you too

[Anonymous]

Hi @Peavey ,

You can try formula like below to create calculate column and measure:

SubTaskProgress = IF(NOT(ISBLANK([Id Main task])), [Progress], BLANK())

AverageProgress = 
CALCULATE(
    AVERAGE([SubTaskProgress]),
    ALLEXCEPT(TableName, TableName[Id Main task])
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

what's the expected output based on the sample data you provided?

[vojtechsima · Accepted solution]

Hello, @Peavey 
Try something like this:

Average progress = 
var currentId = SELECTEDVALUE('Table'[Id])
var averageSubtasks = AVERAGEX(FILTER(ALL('Table'), 'Table'[     Id Main task] = currentId), 'Table'[     Progress])

return averageSubtasks