Forum Discussion

Peavey's avatar
Peavey
Helper III
1 year ago
Solved

Calculate based on other columns

Hello,

 

I have this similar dataset: 

IdTask     Progress     Id Main task
1Main task 1     ? 
2Sub task 1     55     1
3Sub task 2     20     1
4Main task 2     ? 
5Sub task 1     10     4
6Sub task 2     0     4
7Sub task 3     95     4
8Main task 3     ? 
9Sub 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

Need Help
Tips and Tricks
  • vojtechsima's avatar
    vojtechsima
    1 year ago

    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

5 Replies

  • Peavey's avatar
    Peavey
    Helper III
    1 year ago

    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's avatar
    vojtechsima
    Super User
    1 year ago

    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
  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.