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: 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

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

6 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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