Solved: Performing analysis on grouped data?

prob_powerBI · ‎12-14-2022

I have some energy consumtption data (below) that i'm suing as a training dataset to help me learn PowerBI.

I've come a decent way but have stumbled upon a problem that ive not been able to solve with a little research. I'd appreciate some help pls.

The data are in half hours, so ive used groups (bins) to collate the data per day. I then want to work with the binned data, for example to use an IF statment to highlight high consumption days. What would be a good strategy to do this? I can Sum the consumption data in a table visulaisation vs the binned days, but cant then create a new 'column' in such a table. Should i output a table? I tried exporting but then it is dead and not updated. Can i create a live link to another table? Or is there a better way to do this?

Consumption (kWh)	Start	End
0.085	2022-12-09T00:00:00+00:00	2022-12-09T00:30:00+00:00
0.12	2022-12-09T00:30:00+00:00	2022-12-09T01:00:00+00:00
0.46	2022-12-09T01:00:00+00:00	2022-12-09T01:30:00+00:00
0.112	2022-12-09T01:30:00+00:00	2022-12-09T02:00:00+00:00
0.104	2022-12-09T02:00:00+00:00	2022-12-09T02:30:00+00:00
0.094	2022-12-09T02:30:00+00:00	2022-12-09T03:00:00+00:00
0.634	2022-12-09T03:00:00+00:00	2022-12-09T03:30:00+00:00
0.075	2022-12-09T03:30:00+00:00	2022-12-09T04:00:00+00:00
0.102	2022-12-09T04:00:00+00:00	2022-12-09T04:30:00+00:00
0.088	2022-12-09T04:30:00+00:00	2022-12-09T05:00:00+00:00
0.084	2022-12-09T05:00:00+00:00	2022-12-09T05:30:00+00:00
0.157	2022-12-09T05:30:00+00:00	2022-12-09T06:00:00+00:00
0.13	2022-12-09T06:00:00+00:00	2022-12-09T06:30:00+00:00
0.362	2022-12-09T06:30:00+00:00	2022-12-09T07:00:00+00:00
0.208	2022-12-09T07:00:00+00:00	2022-12-09T07:30:00+00:00
0.492	2022-12-09T07:30:00+00:00	2022-12-09T08:00:00+00:00
0.259	2022-12-09T08:00:00+00:00	2022-12-09T08:30:00+00:00
0.29	2022-12-09T08:30:00+00:00	2022-12-09T09:00:00+00:00
0.736	2022-12-09T09:00:00+00:00	2022-12-09T09:30:00+00:00
0.21	2022-12-09T09:30:00+00:00	2022-12-09T10:00:00+00:00
0.213	2022-12-09T10:00:00+00:00	2022-12-09T10:30:00+00:00
0.198	2022-12-09T10:30:00+00:00	2022-12-09T11:00:00+00:00
0.21	2022-12-09T11:00:00+00:00	2022-12-09T11:30:00+00:00
0.14	2022-12-09T11:30:00+00:00	2022-12-09T12:00:00+00:00
0.158	2022-12-09T12:00:00+00:00	2022-12-09T12:30:00+00:00
0.139	2022-12-09T12:30:00+00:00	2022-12-09T13:00:00+00:00
0.133	2022-12-09T13:00:00+00:00	2022-12-09T13:30:00+00:00
0.141	2022-12-09T13:30:00+00:00	2022-12-09T14:00:00+00:00
0.258	2022-12-09T14:00:00+00:00	2022-12-09T14:30:00+00:00
0.178	2022-12-09T14:30:00+00:00	2022-12-09T15:00:00+00:00
0.135	2022-12-09T15:00:00+00:00	2022-12-09T15:30:00+00:00
0.178	2022-12-09T15:30:00+00:00	2022-12-09T16:00:00+00:00
0.267	2022-12-09T16:00:00+00:00	2022-12-09T16:30:00+00:00
0.27	2022-12-09T16:30:00+00:00	2022-12-09T17:00:00+00:00
0.203	2022-12-09T17:00:00+00:00	2022-12-09T17:30:00+00:00
0.205	2022-12-09T17:30:00+00:00	2022-12-09T18:00:00+00:00
1.108	2022-12-09T18:00:00+00:00	2022-12-09T18:30:00+00:00
0.741	2022-12-09T18:30:00+00:00	2022-12-09T19:00:00+00:00
0.286	2022-12-09T19:00:00+00:00	2022-12-09T19:30:00+00:00
0.273	2022-12-09T19:30:00+00:00	2022-12-09T20:00:00+00:00
0.239	2022-12-09T20:00:00+00:00	2022-12-09T20:30:00+00:00
0.191	2022-12-09T20:30:00+00:00	2022-12-09T21:00:00+00:00
0.186	2022-12-09T21:00:00+00:00	2022-12-09T21:30:00+00:00
0.174	2022-12-09T21:30:00+00:00	2022-12-09T22:00:00+00:00
0.125	2022-12-09T22:00:00+00:00	2022-12-09T22:30:00+00:00
0.147	2022-12-09T22:30:00+00:00	2022-12-09T23:00:00+00:00
0.121	2022-12-09T23:00:00+00:00	2022-12-09T23:30:00+00:00
0.089	2022-12-09T23:30:00+00:00	2022-12-10T00:00:00+00:00
0.084	2022-12-10T00:00:00+00:00	2022-12-10T00:30:00+00:00
0.063	2022-12-10T00:30:00+00:00	2022-12-10T01:00:00+00:00
0.093	2022-12-10T01:00:00+00:00	2022-12-10T01:30:00+00:00
0.163	2022-12-10T01:30:00+00:00	2022-12-10T02:00:00+00:00
0.133	2022-12-10T02:00:00+00:00	2022-12-10T02:30:00+00:00
0.484	2022-12-10T02:30:00+00:00	2022-12-10T03:00:00+00:00
0.113	2022-12-10T03:00:00+00:00	2022-12-10T03:30:00+00:00
0.093	2022-12-10T03:30:00+00:00	2022-12-10T04:00:00+00:00
0.096	2022-12-10T04:00:00+00:00	2022-12-10T04:30:00+00:00
0.634	2022-12-10T04:30:00+00:00	2022-12-10T05:00:00+00:00
0.092	2022-12-10T05:00:00+00:00	2022-12-10T05:30:00+00:00
0.069	2022-12-10T05:30:00+00:00	2022-12-10T06:00:00+00:00
0.136	2022-12-10T06:00:00+00:00	2022-12-10T06:30:00+00:00
0.313	2022-12-10T06:30:00+00:00	2022-12-10T07:00:00+00:00
0.747	2022-12-10T07:00:00+00:00	2022-12-10T07:30:00+00:00
0.951	2022-12-10T07:30:00+00:00	2022-12-10T08:00:00+00:00
0.288	2022-12-10T08:00:00+00:00	2022-12-10T08:30:00+00:00
0.39	2022-12-10T08:30:00+00:00	2022-12-10T09:00:00+00:00
0.22	2022-12-10T09:00:00+00:00	2022-12-10T09:30:00+00:00
0.201	2022-12-10T09:30:00+00:00	2022-12-10T10:00:00+00:00
0.196	2022-12-10T10:00:00+00:00	2022-12-10T10:30:00+00:00
0.282	2022-12-10T10:30:00+00:00	2022-12-10T11:00:00+00:00
0.119	2022-12-10T11:00:00+00:00	2022-12-10T11:30:00+00:00
0.09	2022-12-10T11:30:00+00:00	2022-12-10T12:00:00+00:00
0.092	2022-12-10T12:00:00+00:00	2022-12-10T12:30:00+00:00
0.13	2022-12-10T12:30:00+00:00	2022-12-10T13:00:00+00:00
0.179	2022-12-10T13:00:00+00:00	2022-12-10T13:30:00+00:00
0.196	2022-12-10T13:30:00+00:00	2022-12-10T14:00:00+00:00
0.175	2022-12-10T14:00:00+00:00	2022-12-10T14:30:00+00:00
0.138	2022-12-10T14:30:00+00:00	2022-12-10T15:00:00+00:00
0.125	2022-12-10T15:00:00+00:00	2022-12-10T15:30:00+00:00
0.093	2022-12-10T15:30:00+00:00	2022-12-10T16:00:00+00:00
0.201	2022-12-10T16:00:00+00:00	2022-12-10T16:30:00+00:00
0.165	2022-12-10T16:30:00+00:00	2022-12-10T17:00:00+00:00
0.074	2022-12-10T17:00:00+00:00	2022-12-10T17:30:00+00:00
0.1	2022-12-10T17:30:00+00:00	2022-12-10T18:00:00+00:00
0.064	2022-12-10T18:00:00+00:00	2022-12-10T18:30:00+00:00
0.856	2022-12-10T18:30:00+00:00	2022-12-10T19:00:00+00:00
0.393	2022-12-10T19:00:00+00:00	2022-12-10T19:30:00+00:00
0.346	2022-12-10T19:30:00+00:00	2022-12-10T20:00:00+00:00
0.227	2022-12-10T20:00:00+00:00	2022-12-10T20:30:00+00:00
0.187	2022-12-10T20:30:00+00:00	2022-12-10T21:00:00+00:00
0.17	2022-12-10T21:00:00+00:00	2022-12-10T21:30:00+00:00
0.156	2022-12-10T21:30:00+00:00	2022-12-10T22:00:00+00:00
0.161	2022-12-10T22:00:00+00:00	2022-12-10T22:30:00+00:00
0.127	2022-12-10T22:30:00+00:00	2022-12-10T23:00:00+00:00
0.108	2022-12-10T23:00:00+00:00	2022-12-10T23:30:00+00:00
0.067	2022-12-10T23:30:00+00:00	2022-12-11T00:00:00+00:00
0.101	2022-12-11T00:00:00+00:00	2022-12-11T00:30:00+00:00
0.059	2022-12-11T00:30:00+00:00	2022-12-11T01:00:00+00:00
0.1	2022-12-11T01:00:00+00:00	2022-12-11T01:30:00+00:00
0.426	2022-12-11T01:30:00+00:00	2022-12-11T02:00:00+00:00
0.096	2022-12-11T02:00:00+00:00	2022-12-11T02:30:00+00:00
0.273	2022-12-11T02:30:00+00:00	2022-12-11T03:00:00+00:00
0.149	2022-12-11T03:00:00+00:00	2022-12-11T03:30:00+00:00
0.657	2022-12-11T03:30:00+00:00	2022-12-11T04:00:00+00:00
0.065	2022-12-11T04:00:00+00:00	2022-12-11T04:30:00+00:00
0.097	2022-12-11T04:30:00+00:00	2022-12-11T05:00:00+00:00
0.067	2022-12-11T05:00:00+00:00	2022-12-11T05:30:00+00:00
0.086	2022-12-11T05:30:00+00:00	2022-12-11T06:00:00+00:00
0.126	2022-12-11T06:00:00+00:00	2022-12-11T06:30:00+00:00
0.286	2022-12-11T06:30:00+00:00	2022-12-11T07:00:00+00:00
0.199	2022-12-11T07:00:00+00:00	2022-12-11T07:30:00+00:00
0.14	2022-12-11T07:30:00+00:00	2022-12-11T08:00:00+00:00
0.178	2022-12-11T08:00:00+00:00	2022-12-11T08:30:00+00:00
0.691	2022-12-11T08:30:00+00:00	2022-12-11T09:00:00+00:00
1.296	2022-12-11T09:00:00+00:00	2022-12-11T09:30:00+00:00
0.882	2022-12-11T09:30:00+00:00	2022-12-11T10:00:00+00:00
1.563	2022-12-11T10:00:00+00:00	2022-12-11T10:30:00+00:00
0.591	2022-12-11T10:30:00+00:00	2022-12-11T11:00:00+00:00
2.356	2022-12-11T11:00:00+00:00	2022-12-11T11:30:00+00:00
2.138	2022-12-11T11:30:00+00:00	2022-12-11T12:00:00+00:00
1.632	2022-12-11T12:00:00+00:00	2022-12-11T12:30:00+00:00
0.533	2022-12-11T12:30:00+00:00	2022-12-11T13:00:00+00:00
0.227	2022-12-11T13:00:00+00:00	2022-12-11T13:30:00+00:00
0.803	2022-12-11T13:30:00+00:00	2022-12-11T14:00:00+00:00
0.263	2022-12-11T14:00:00+00:00	2022-12-11T14:30:00+00:00
0.558	2022-12-11T14:30:00+00:00	2022-12-11T15:00:00+00:00
0.366	2022-12-11T15:00:00+00:00	2022-12-11T15:30:00+00:00
0.499	2022-12-11T15:30:00+00:00	2022-12-11T16:00:00+00:00
0.397	2022-12-11T16:00:00+00:00	2022-12-11T16:30:00+00:00
0.387	2022-12-11T16:30:00+00:00	2022-12-11T17:00:00+00:00
0.37	2022-12-11T17:00:00+00:00	2022-12-11T17:30:00+00:00
0.364	2022-12-11T17:30:00+00:00	2022-12-11T18:00:00+00:00
0.268	2022-12-11T18:00:00+00:00	2022-12-11T18:30:00+00:00
0.236	2022-12-11T18:30:00+00:00	2022-12-11T19:00:00+00:00
0.252	2022-12-11T19:00:00+00:00	2022-12-11T19:30:00+00:00
0.251	2022-12-11T19:30:00+00:00	2022-12-11T20:00:00+00:00
0.222	2022-12-11T20:00:00+00:00	2022-12-11T20:30:00+00:00
0.183	2022-12-11T20:30:00+00:00	2022-12-11T21:00:00+00:00
0.137	2022-12-11T21:00:00+00:00	2022-12-11T21:30:00+00:00
0.103	2022-12-11T21:30:00+00:00	2022-12-11T22:00:00+00:00
0.123	2022-12-11T22:00:00+00:00	2022-12-11T22:30:00+00:00
0.084	2022-12-11T22:30:00+00:00	2022-12-11T23:00:00+00:00
0.109	2022-12-11T23:00:00+00:00	2022-12-11T23:30:00+00:00
0.074	2022-12-11T23:30:00+00:00	2022-12-12T00:00:00+00:00
0.269	2022-12-12T00:00:00+00:00	2022-12-12T00:30:00+00:00
0.265	2022-12-12T00:30:00+00:00	2022-12-12T01:00:00+00:00
0.082	2022-12-12T01:00:00+00:00	2022-12-12T01:30:00+00:00
0.109	2022-12-12T01:30:00+00:00	2022-12-12T02:00:00+00:00
0.548	2022-12-12T02:00:00+00:00	2022-12-12T02:30:00+00:00
0.085	2022-12-12T02:30:00+00:00	2022-12-12T03:00:00+00:00
0.09	2022-12-12T03:00:00+00:00	2022-12-12T03:30:00+00:00
0.064	2022-12-12T03:30:00+00:00	2022-12-12T04:00:00+00:00
0.085	2022-12-12T04:00:00+00:00	2022-12-12T04:30:00+00:00
0.057	2022-12-12T04:30:00+00:00	2022-12-12T05:00:00+00:00
0.075	2022-12-12T05:00:00+00:00	2022-12-12T05:30:00+00:00
0.095	2022-12-12T05:30:00+00:00	2022-12-12T06:00:00+00:00
0.105	2022-12-12T06:00:00+00:00	2022-12-12T06:30:00+00:00
0.123	2022-12-12T06:30:00+00:00	2022-12-12T07:00:00+00:00
0.288	2022-12-12T07:00:00+00:00	2022-12-12T07:30:00+00:00
0.162	2022-12-12T07:30:00+00:00	2022-12-12T08:00:00+00:00
0.356	2022-12-12T08:00:00+00:00	2022-12-12T08:30:00+00:00
0.427	2022-12-12T08:30:00+00:00	2022-12-12T09:00:00+00:00
0.287	2022-12-12T09:00:00+00:00	2022-12-12T09:30:00+00:00
0.309	2022-12-12T09:30:00+00:00	2022-12-12T10:00:00+00:00
0.225	2022-12-12T10:00:00+00:00	2022-12-12T10:30:00+00:00
0.18	2022-12-12T10:30:00+00:00	2022-12-12T11:00:00+00:00
0.177	2022-12-12T11:00:00+00:00	2022-12-12T11:30:00+00:00
0.172	2022-12-12T11:30:00+00:00	2022-12-12T12:00:00+00:00
0.353	2022-12-12T12:00:00+00:00	2022-12-12T12:30:00+00:00
0.328	2022-12-12T12:30:00+00:00	2022-12-12T13:00:00+00:00
0.217	2022-12-12T13:00:00+00:00	2022-12-12T13:30:00+00:00
0.246	2022-12-12T13:30:00+00:00	2022-12-12T14:00:00+00:00
0.294	2022-12-12T14:00:00+00:00	2022-12-12T14:30:00+00:00
0.22	2022-12-12T14:30:00+00:00	2022-12-12T15:00:00+00:00
0.22	2022-12-12T15:00:00+00:00	2022-12-12T15:30:00+00:00
0.323	2022-12-12T15:30:00+00:00	2022-12-12T16:00:00+00:00
0.303	2022-12-12T16:00:00+00:00	2022-12-12T16:30:00+00:00
0.261	2022-12-12T16:30:00+00:00	2022-12-12T17:00:00+00:00
0.289	2022-12-12T17:00:00+00:00	2022-12-12T17:30:00+00:00
0.47	2022-12-12T17:30:00+00:00	2022-12-12T18:00:00+00:00
0.26	2022-12-12T18:00:00+00:00	2022-12-12T18:30:00+00:00
0.252	2022-12-12T18:30:00+00:00	2022-12-12T19:00:00+00:00
0.232	2022-12-12T19:00:00+00:00	2022-12-12T19:30:00+00:00
0.205	2022-12-12T19:30:00+00:00	2022-12-12T20:00:00+00:00
0.222	2022-12-12T20:00:00+00:00	2022-12-12T20:30:00+00:00
0.13	2022-12-12T20:30:00+00:00	2022-12-12T21:00:00+00:00
0.156	2022-12-12T21:00:00+00:00	2022-12-12T21:30:00+00:00
0.128	2022-12-12T21:30:00+00:00	2022-12-12T22:00:00+00:00
0.097	2022-12-12T22:00:00+00:00	2022-12-12T22:30:00+00:00
0.097	2022-12-12T22:30:00+00:00	2022-12-12T23:00:00+00:00
0.065	2022-12-12T23:00:00+00:00	2022-12-12T23:30:00+00:00
0.089	2022-12-12T23:30:00+00:00	2022-12-13T00:00:00+00:00

v-xinruzhu-msft · ‎12-14-2022

Hi @prob_powerBI

Do you mean to sum the consumption of each day according to the date, and then divide the data into high consumption or low consumption, and then calculate the sum of consumption for each category according to this category?

Here is a test file I created based on this understanding,

1.create a new column to sum consumption of each day

Column = SUMX(FILTER('Table (2)','Table (2)'[Text Before Delimiter]=EARLIER('Table (2)'[Text Before Delimiter])),[Consumption (kwh)])

2.creata a new table to summarize the data(the type definition is just for the test)

Table 2 = var a=SUMMARIZE('Table (2)','Table (2)'[Text Before Delimiter],[Column])

var b=ADDCOLUMNS(a,"Type",SWITCH(TRUE(),[Column]>=11,"high",[Column]<10.6,"low"))

return b.

3.Sum it by the type

^{Best Regards,}

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

View solution in original post

prob_powerBI · ‎12-29-2022

Works great! Thanks. Some new tricks for me to learn there.

v-xinruzhu-msft · ‎12-14-2022