Forum Discussion

Dcichy's avatar
Dcichy
New Member
2 years ago
Solved

How to create average formule based on different data

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: t...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
ryan_mayu
Super User
2 years ago

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's avatar
    Dcichy
    New Member
    2 years ago

    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's avatar
      ryan_mayu
      Super User
      2 years ago

      Dcichy 

      you can use sum(days) 

       

      not sure if this is the output that you want

      • Dcichy's avatar
        Dcichy
        New Member
        2 years ago

        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.