Forum Discussion

SUTTY's avatar
SUTTY
Frequent Visitor
4 years ago
Solved

Average for different groups instead of total data

Hello,

Tried a few different things this afternoon but to no avail.

I'm taking data from a folder full of Excel files containing the following data:

NameSessionDurationDistance
AMatchday7810000
BMatchday9312000
CMatchday9313400
DMatchday9312200
EMatchday9311000
Atraining608000
Btraining608000
Ctraining608000
Dtraining608000
Etraining608000
AMatchday9312000
BMatchday9313000
CMatchday9311000
DMatchday719000
EMatchday9310000

 

I'm looking to present this data in a vizualisation that shows the Average Distance each person covered, when the session is a matchday and the duration was longer than 90. I'm currently using: Measure = Calculate(AVERAGE(Distance), [session]="Matchday"&&[duration]>90) which is giving the total average output in the table but the individual names are remaining blank. I can't figure out how to edit my formula in a way that shows each person's individualised average data in the table, which I then want to use to take each training distance as a % in a subsequent measure.

Any help would be much appreciated!

Many Thanks

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    Hi SUTTY ,

     

    According to your description, you need to add a grouping condition. Refer to the following test results:

    Column = 
IF (
    'Table (2)'[Session] = "training",
    BLANK (),
    CALCULATE (
        AVERAGE ( 'Table (2)'[Distance] ),
        FILTER (
            ALL ( 'Table (2)' ),
            'Table (2)'[Session] = "Matchday"
                && 'Table (2)'[Duration] > 90
                && 'Table (2)'[Name] = EARLIER ( 'Table (2)'[Name] )
        )
    )
)


    If the problem is still not resolved, please provide detailed error information and a screenshot of the desired result. Looking forward to your reply.


    Best Regards,
    Henry


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

3 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    Hi SUTTY ,

     

    What is the final result you want to achieve? How do you want to use that result?

     

    Can you please share some examples of the result you need?

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi SUTTY ,

     

    According to your description, you need to add a grouping condition. Refer to the following test results:

    Column = 
IF (
    'Table (2)'[Session] = "training",
    BLANK (),
    CALCULATE (
        AVERAGE ( 'Table (2)'[Distance] ),
        FILTER (
            ALL ( 'Table (2)' ),
            'Table (2)'[Session] = "Matchday"
                && 'Table (2)'[Duration] > 90
                && 'Table (2)'[Name] = EARLIER ( 'Table (2)'[Name] )
        )
    )
)


    If the problem is still not resolved, please provide detailed error information and a screenshot of the desired result. Looking forward to your reply.


    Best Regards,
    Henry


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

    • SUTTY's avatar
      SUTTY
      Frequent Visitor
      4 years ago

      That's great thanks Henry!

      I think my issue was I was trying to get it as a measure but completely forgot how easy using columns are!