help

[PSRai]

currently have data where by its measuring usage of each electrical sockets, Energy by date time (seconds).

I need to create a new column as below. Cant be done in excel due to the amount of data/rows

Date/Time                         Socket ID                   Energy                       New Column

18/10/2019 - 13:01:01          1                                0.1                                 0.1
18/10/2019 - 13:01:05          1                                0.3                                 0.3
18/10/2019 - 13:01:07          1                                0.5                                 0.5
18/10/2019 - 13:01:09          1                                0.6                                 0.6
18/10/2019 - 13:01:11          1                                1.0                                 1.0

18/10/2019 - 13:01:15          1                                0.0                                 1.0
18/10/2019 - 13:01:18          1                                0.3                                 1.3
18/10/2019 - 13:01:20          1                                0.5                                 1.5
18/10/2019 - 13:01:22          1                                0.7                                 1.7

As you can see the Energy column reading increases at each reading from 18/10/2019 - 13:01:01 however when the socket is switched off the reading then sets to 0.0. what I need to do is bridge the gap. I need to create a new column as above and say *IF the current reading is less then the MAX reading then ADD MAX reading to current reading*. So in this case 1.0 is the MAX reading and lets say 0.0 is current I need to add 1.0 to 0.0.

also their are multiple sockets so it would look more like this,

Date/Time                               Socket ID                Energy                       New Column

18/10/2019 - 13:01:01                 1                           0.1                                 0.1
18/10/2019 - 13:01:01                 2                           4.3                                 4.3
18/10/2019 - 13:01:01                 3                           5.5                                 5.5
18/10/2019 - 13:01:01                 4                           1.6                                 1.6
18/10/2019 - 13:01:01                 5                           1.2                                 1.2

18/10/2019 - 13:01:15                1                            0.0                                 1.0
18/10/2019 - 13:01:15                2                            0.1                                 4.4
18/10/2019 - 13:01:15                3                            0.2                                 5.7
18/10/2019 - 13:01:15                4                            0.0                                 1.6
18/10/2019 - 13:01:15                5                            0.3                                 1.5

Hope this makes sense?

[PSRai]

Thanks for your reply 

when i enter the following code in new column, (changed "Test" to "readings) the "Energy" 

Column =

VAR MaxEnergy = CALCULATE(MAX(readings[Energy]),ALLEXCEPT(readings,readings[socket_id]))

RETURN IF(readings[Energy]<MaxEnergy,readings[Energy]+MaxEnergy,readings[Energy])

the Energy in the RETURN IF column highlight RED (error)

"A single value for column 'Energy' in table 'readings' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result"

[PSRai]

Hi,

I have applied your syntax, the column is applying the MAX reading to all the column however I only need to apply the MAX reading when the new reading is below the MAX reading, below is what I'm looking for,

Date Time Socket             Socket ID             Energy                 Output

18/10/2019 13:01:01       1                              0.10                        0.10

19/10/2019 13:01:05       1                              0.30                        0.30

20/10/2019 13:01:07       1                              0.50                        0.50

21/10/2019 13:01:09       1                              0.60                        0.60

22/10/2019 13:01:11       1                              1.00                        1.00

23/10/2019 13:01:15       1                              0.00                        1.00

24/10/2019 13:01:18       1                              0.30                        1.30

25/10/2019 13:01:20       1                              0.50                        1.50

26/10/2019 13:01:22       1                              0.70                        1.70

27/10/2019 13:01:25       1                              0.00                        1.00

28/10/2019 13:01:26       1                              0.20                        1.20

[v-eachen-msft]

Hi @PSRai 

You could refer to the following DAX:

Output =
VAR maxenerge =
    CALCULATE ( MAX ( 'Table'[Energy] ), ALLEXCEPT ( 'Table', 'Table'[Socket ID] ) )
RETURN
    IF (
        'Table'[Date Time Socket]
            <= CALCULATE (
                LOOKUPVALUE ( 'Table'[Date Time Socket], 'Table'[Energy], maxenerge ),
                ALLEXCEPT ( 'Table', 'Table'[Socket ID] )
            ),
        'Table'[Energy],
        maxenerge + 'Table'[Energy]
    )

Here is the result:

[PSRai]

Thank you 

Im getting the following error message 

"A table of multiple values was supplied where a single value was expected"

can you advise please?.

[v-eachen-msft]

Hi @PSRai ,

You could add an index column in the query editor and replace 'Table'[Date Time Socket] with 'Table'[Index]( index column ).

[PSRai]

Can anyone urgently help with my query please?

[Anonymous]

@PSRai
If I understood it clearly please try below code

Column =

VAR MaxEnergy = CALCULATE(MAX(Test[Energy]),ALLEXCEPT(Test,Test[Socket ID]))

RETURN IF(Test[Energy]<MaxEnergy,Test[Energy]+MaxEnergy,Test[Energy])