Forum Discussion

miemer's avatar
miemer
Regular Visitor
7 years ago
Solved

Monthly average calculation

Hi everyone, i'm looking for help with a power-bi calculation:

I have a table with 4 columns (date, time, Kiln, Paf) of which I want to calculate the monthly average of the PAF column.

Here is an example of a table:

DataOraFornoNotePAF
15/03/2019 00:001899-12-30 00:00:0010Accensione 12/03/201929,34
18/03/2019 00:001899-12-30 00:00:004 16,79
19/03/2019 00:001899-12-30 00:00:006 12,58
20/03/2019 00:001899-12-30 00:00:008 20,6
20/03/2019 00:001899-12-30 08:15:236 12,09
21/03/2019 00:001899-12-30 00:00:009 7,31
22/03/2019 00:001899-12-30 00:00:006 7,72
25/03/2019 00:001899-12-30 08:00:344 3,3
26/03/2019 00:001899-12-30 00:00:005 6,09
26/03/2019 00:001899-12-30 00:00:0010 18,4
27/03/2019 00:001899-12-30 14:00:007 7,79
28/03/2019 00:001899-12-30 08:00:0010 12,16
28/03/2019 00:001899-12-30 08:00:008 14,62
29/03/2019 00:001899-12-30 08:30:009 7,47
01/04/2019 00:001899-12-30 00:00:005 2,16
10/04/2019 00:001899-12-30 09:22:004 9,51
11/04/2019 00:001899-12-30 00:00:009prelievo mattina14,8
15/04/2019 00:001899-12-30 14:34:268 3,3
16/04/2019 00:001899-12-30 14:34:066 14,16
17/04/2019 00:001899-12-30 14:32:327 17,22
19/04/2019 00:001899-12-30 11:37:1410prelievo h. 8.0011,52
23/04/2019 00:001899-12-30 14:17:194 10,42
24/04/2019 00:001899-12-30 14:17:509 3,32
29/04/2019 00:001899-12-30 00:00:0010 6,53
30/04/2019 00:001899-12-30 00:00:004mattina3,05
30/04/2019 00:001899-12-30 08:43:378mattina5,87
03/05/2019 00:001899-12-30 00:00:004mattina5,96

Thank you for your valuable support

  • Anonymous's avatar
    Anonymous
    7 years ago

    I couldn't do this way, but i have another solution that might help you.

     

    Create a table

    Table = SUMMARIZE(Table1;Table1[Date:])
    Create a column on that new table
    Column = CALCULATE(AVERAGE(Table1[Values:]);FILTER(Table1;Table1[Date:]='Table'[Date:]))
     
    This should get you this result.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi buddy,

     

    Try create a measure with this DAX:

    Measure = AVERAGE(Table1[PAF])
     
     
    The trick here is, everytime you use this measure just put the date on the month format from his hierarchy.
     
    Any questions, ask ;)
    • miemer's avatar
      miemer
      Regular Visitor
      7 years ago

      Thanks, you've been very helpful.

      However, I ask you something (maybe) more complex: can I create a column in the table with the monthly average value PAF, so that it is automatically filled in?

       

      Here is the example table I would like to fill in automatically with the average column based on the date, line by line

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I couldn't do this way, but i have another solution that might help you.

         

        Create a table

        Table = SUMMARIZE(Table1;Table1[Date:])
        Create a column on that new table
        Column = CALCULATE(AVERAGE(Table1[Values:]);FILTER(Table1;Table1[Date:]='Table'[Date:]))
         
        This should get you this result.