Forum Discussion

CalvinL's avatar
CalvinL
Helper II
6 years ago
Solved

Calculate AVG value between certain dates

Hi all,

 

Looking to calculate the average of a value between certain dates. Below is a sample of the data that I have.

 

Date AttributeValue
01/03/2020 00:00:00 HH147
28/03/2020 00:00:00 HH158
15/03/2020 00:00:00 HH178
05/04/2021 00:00:30 HH266
21/04/2021 00:00:30 HH272

 

 

My intended result is to display the average value for each HH (half hour) for the entire month.

 

Example of the result:

Average of HH1 March Average of HH2 April
61 69

 

Thank you.

Need Help
  • Pragati11's avatar
    Pragati11
    6 years ago

    Hi CalvinL ,

     

    Looking at your sample data, you can do the following:

    • Create a table visual in Power BI
    • Move ATTRIBUTE column to it.
    • Move VALUE column to it with summarisation set to Average

     

    If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

     

    Thanks,

    Pragati

5 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    Hi CalvinL ,

     

    Looking at your sample data, you can do the following:

    • Create a table visual in Power BI
    • Move ATTRIBUTE column to it.
    • Move VALUE column to it with summarisation set to Average

     

    If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

     

    Thanks,

    Pragati

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    CalvinL you can easily achieve by following:

     

    use matrix visual

    put attribute on columns

    value on values and choose aggregation to average

     

    or create a measure

     

    Avg = AVERAGE ( Table[Value] )

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi CalvinL , you can easily do this !!!
    1. select  matrix visual.
    2. put columns value to Value.
    3. Select average from values i.e..

    Hope You will get this....
    Thanks

     

    If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution!

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi  CalvinL ,

     

    You can create 2 calculated columns as below:

     

    Month = FORMAT('Table'[Date.1],"MMMM")
    Column = "Average of" &" "&'Table'[Attribute]&" "&'Table'[Month]

     

    Then create a matrix visual,put column in the field of "column" ,value in the field of "value",right click on value,select "average":

    And you will see:

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
  • CalvinL's avatar
    CalvinL
    Helper II
    6 years ago

    Thanks guys, these solutions worked. Much appreciated!