Question about finding max value in dataset

[Rinto]

Hi all,

i have a question about finding the max value.

My dataset looks like the following:

Name	Date	Time	Full Hour	Unique key	Value
Theo	01-01-2020	00:00	00:00	Theo 01-01-2020 00:00	15
Theo	01-01-2020	00:15	00:00	Theo 01-01-2020 00:00	30
Theo	01-01-2020	00:30	00:00	Theo 01-01-2020 00:00	25
Theo	01-01-2020	00:45	00:00	Theo 01-01-2020 00:00	40
Theo	01-01-2020	01:00	01:00	Theo 01-01-2020 01:00	15
Theo	01-01-2020	01:15	01:00	Theo 01-01-2020 01:00	20
Theo	01-01-2020	01:30	01:00	Theo 01-01-2020 01:00	35
Theo	01-01-2020	01:45	01:00	Theo 01-01-2020 01:00	30
Theo	01-01-2020	02:00	02:00	Theo 01-01-2020 02:00	25
Frank	01-01-2020	00:00	00:00	Frank 01-01-2020 00:00	10

I have made a new table in which i have grouped the column unique key in a way that each unique key is there only once. And now i want to add a column with the max value per hour per uniqe key. So the table will look as following:

Unique key	MaxValue
Theo 01-01-2020 00:00	40
Theo 01-01-2020 01:00	35
Theo 01-01-2020 02:00	25
Frank 01-01-2020 00:00	10

Can someone help me? cause i can't figure out the formula needed here.

The column Unique key contains text.

Thanks in advance!

[v-yalanwu-msft]

Hi， @Rinto ;

Sorry, I may be a little poor in understanding. Please give me the original table and the output result, so that I can have a clearer understanding of your logic.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rinto]

Hi, @v-yalanwu-msft 

It probibly is a bit confusing. Therefore i will make a better description of what i'am trying to do.

I have two sets of data:

- 1 set of data consists of about 200 meters with quarterly (electricity) or hourly data (gas) for a whole year

- 1 set of data consists of about 100 buildings with caracteristics of these building including the meters which are part of these buildings. in many cases a building has 1 electricity and 1 gas meter, but sometimes a building has for instance two electricity meters.

What I want to do with this data is:

1. sum up the amount of electricity used in an hour with the gas used in an hour. Herefor I'm looking for the maximum use of electricity in every hour of the year, which i will mutiply times four. And I am looking for the maximum amount of gas used in every hour of the year. I have made a new column in my datasheet in which I have converted quarters into hours (so 0:00, 0:15, 0:30 and 0:45 are now all 0:00). Because I have 200 different meters I have made a new column in which I have grouped the name of the meter, the date and the timestamp (full hours).  Based upon this data I have created a new table in which I have used the function groupby so that I have left 200 (meters) x 8760 (hours per year) unique datapoints.

This table I want to expend with the max value for electricity consumption per hour and the max value for gas consumption per hour.

This looks as following:

datasheet

Product	Date	Time	Consumption	Timestamp (hour)	New measure
Electricity001	01-01-2021	00:00	0,51	00:00	Electricity001 01-01-2021 00:00
Electricity001	01-01-2021	00:15	0,58	00:00	Electricity001 01-01-2021 00:00
Electricity001	01-01-2021	00:30	0,62	00:00	Electricity001 01-01-2021 00:00
Electricity001	01-01-2021	00:45	0,53	00:00	Electricity001 01-01-2021 00:00
Electricity001	...	...	...	...	...
Electricity002	...	...	...	...	...
Gas001	01-01-2021	00:00	2,3	00:00	Gas001 01-01-2021 00:00
Gas001	01-01-2021	01:00	3,4	01:00	Gas001 01-01-2021 01:00

What I want to create in a new table looks like:

Product date timestamp hourly	Max consumption
Electricity001 01-01-2021 00:00	0,62
Electricity001 01-01-2021 01:00	...
Electricity001 01-01-2021 02:00	...
...	...
Gas001 01-01-2021 00:00	2,3
Gas001 01-01-2021 01:00	3,4
...
...

2. My second database contains the data from which I know which meter belongs to which building. Based upon this knowledge I want to expand my former new table with the data of which building belongs to a specific meter. So the table looks like this: This is what I have allready build, lacking the maximum value 🙂

Product date timestamp hourly	Max consumption	Building	New unique measure
Electricity001 01-01-2021 00:00	0,62	Building001	Building001 01-01-2021 00:00
Electricity001 01-01-2021 01:00	0,8	Building001	Building001 01-01-2021 01:00
Electricity001 01-01-2021 02:00	...	Building001	Building 01-01-2021 02:00
...	...	...	...
Gas001 01-01-2021 00:00	2,3	Building001	Building001 01-01-2021 00:00
Gas001 01-01-2021 01:00	3,4	Building001	Building001 01-01-2021 01:00
...		Building002	Building002 ...
...		...	Etc...

From this table I have created a new table in which I used the groupby formula another time but now to create new unique variables containing the name of the building and the date and hourly timestamp (Building001 01-01-2021 00:00, etc..) In this table I want to sum up the max consumption values for electricity and gas per building.

So for instance:

New unique measure	Total consumption
Building001 01-01-2021 00:00	2,92
Building001 01-01-2021 01:00	4,20
Building 01-01-2021 02:00	...
...	...
Building001 01-01-2021 00:00	2,3
Building001 01-01-2021 01:00	3,4
Building002 ...	...
Etc...	...

I hope this clarifies what I am trying to do with the data.

Thanks in advance,

Rinto

[v-yalanwu-msft]

Hi, @Rinto ;

You could create a measure.

max = CALCULATE(MAX('Table'[Value]),FILTER(ALLSELECTED('Table'),[Unique key]=MAX([Unique key])))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rinto]

Thank you @v-yalanwu-msft for your reaction

I am looking for a query with which I can build a new column, because after this new column is created i want to sum up two datastreams to a new unique datacolumn.

For example Frank and Theo live in the same house (adress). After I have found the max measure for a date and time stamp with the name Frank and the same for Theo. With the next step I want to sum up the max value for Frank and Theo at the exact samen date and time stamp, using a new unique code in which the date and time stamp are used in combination with the adress of there home.

Thanks,

Rinto