topic Re: How to create average formule based on different data in DAX Commands and Tips

How to create average formule based on different data

Dcichy — Thu, 20 Jun 2024 09:13:49 GMT

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

Re: How to create average formule based on different data

ryan_mayu — Thu, 20 Jun 2024 09:33:58 GMT

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?

Re: How to create average formule based on different data

Dcichy — Thu, 20 Jun 2024 09:37:50 GMT

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

Re: How to create average formule based on different data

ryan_mayu — Thu, 20 Jun 2024 09:41:09 GMT

Dcichy

you can use sum(days)

not sure if this is the output that you want

Re: How to create average formule based on different data

Dcichy — Thu, 20 Jun 2024 09:50:05 GMT

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.

Re: How to create average formule based on different data

ryan_mayu — Thu, 20 Jun 2024 09:54:31 GMT

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.

Re: How to create average formule based on different data

Anonymous — Tue, 02 Jul 2024 07:18:37 GMT

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