How to create average formule based on different data

[Dcichy]

Hi guys 
I've got tricky question about creating new Average formule. I would like to create formula which will show average of days from specyfic tasks. Tasks should be calculated as for example: task 1 is typed twice so sum of this task equals 12, task 2 lasts 3 days, task  4 lasts 7 days. 

Task              days

1                      4
1                      8
2                      3
3                      7

[Anonymous · Accepted solution]

Hi @Dcichy ,

May I ask if your problem has been solved. If the above reply was helpful, you may consider marking it as solution. If the problem is not yet solved, you can follow the steps below:

Add new measure:

result =
VAR _total =
    CALCULATE (
        SUM ( 'Table'[days] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Task] = SELECTEDVALUE ( 'Table'[Task] ) )
    )
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Task] = SELECTEDVALUE ( 'Table'[Task] ) )
    )
RETURN
    _total / _count

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[ryan_mayu]

@Dcichy 

not clear about this. You want the average. Thne you said for task 1 the sum is 12. Then what's the expected output?

[Dcichy]

Hi @ryan_mayu 
I meant that sum of days for task 1 is 4+8, because this tasks appears twice, and this formula should count it as 12 because this is one task with duration splited into two

[ryan_mayu]

@Dcichy 

you can use sum(days) 

not sure if this is the output that you want

[Dcichy]

I mentioned very simple example. I tried to support myself with ChatGPT but i doesn't work. ChatGPR suggested to creat first column like this:

Averageoftasks=

SUMMARIZE(Tasks, Tasks[TaskName], "Sumdays", SUM(Tasks[task duration ])

)

and then measure:

SredniaDniZadan =
AVERAGEX(
Averageoftasks,
[Sumdays]
)

I received problem like: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[ryan_mayu]

why not create a measuer and use average?

Measure = average('Table'[days])

if this is not what you want, then pls provide the expected output based on the sample data you provided.