Forum Discussion
Solved
Performing analysis on grouped data?
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 |
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.
2 Replies
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.
Works great! Thanks. Some new tricks for me to learn there.
