Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Consumption calculus (energy)

Hi,

I have this table with the values for energy sensors, that registers the value per hour of each sensor, all values in the same column, identified by the sensor id in the same row. I need to have the consumption of each sensor, that can be then filtered in a visual item by location, type, or date, so this sensor_id column has already a relationship with other tables to define all those fields.

 

I'm using this formula:

Consumption =
                          CALCULATE(MAX(table[Value])-                     
                          MIN(table[Value]),
                           ALL(table[Sensor_id]))

 

but all it does is finding the maximum and minimum values of all the sensors in the column, and I need the Sum of all the consumption's, I mean, it should find the max and min of each sensor, do the difference, and then sum it up.

 

Example of the data table:

DateSensor_idValue
03/05/2021 10:00id1550
03/05/2021 11:00id3230
03/05/2021 12:00id21023
03/05/2021 13:00id5112
03/05/2021 14:00id4765
03/05/2021 15:00id1556
03/05/2021 16:00id3234
03/05/2021 17:00id21034
03/05/2021 18:00id5115
03/05/2021 19:00id4777
03/05/2021 20:00id1559
03/05/2021 21:00id3238
03/05/2021 22:00id21056
03/05/2021 23:00id5118
04/05/2021 00:00id4789
04/05/2021 01:00id1650
04/05/2021 02:00id3245
04/05/2021 03:00id21060
04/05/2021 04:00id5123
04/05/2021 05:00id4790

 

I hope I made it clear for you.

Thank you

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    Please check the below picture and the sample pbix file's link down below.

     

     

    Consumption =
    IF (
    ISFILTERED ( 'Table'[Sensor_id] ),
    CALCULATE (
    MAX ( 'Table'[Value] ),
    ALLEXCEPT ( 'Table', 'Table'[Sensor_id], 'Table'[Date] )
    )
    - CALCULATE (
    MIN ( 'Table'[Value] ),
    ALLEXCEPT ( 'Table', 'Table'[Sensor_id], 'Table'[Date] )
    )
    )
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, Anonymous 

    Please check the below picture and the sample pbix file's link down below.

     

     

    Consumption =
    IF (
    ISFILTERED ( 'Table'[Sensor_id] ),
    CALCULATE (
    MAX ( 'Table'[Value] ),
    ALLEXCEPT ( 'Table', 'Table'[Sensor_id], 'Table'[Date] )
    )
    - CALCULATE (
    MIN ( 'Table'[Value] ),
    ALLEXCEPT ( 'Table', 'Table'[Sensor_id], 'Table'[Date] )
    )
    )
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    4 years ago

    Hello

    I have the same doubt, I would like to see the dropbox link, but it does not work.

    Could you help me?

    Thank you.

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    4 years ago

    Thank you very much for the answer,

    I have another doubt, I would also need to take out the calculation of the consumption by datetime, attached screenshot.

    I would appreciate it very much if you could help me.

    Greetings.

    consumo_datetime.JPG