binning
3 Topics
Dynamic Buckets based on Sum value that changes with date slicer
Hello Everyone, I am trying to create a Matrix that dynamically shows count of Salesmen by Product under different buckets as I select different date ranges. The buckets are based on Sales Count as follows - No Sales, 1-10, 11-20, 21-30, and 31-40… (There is a table supporting below) I followed multiple ways from the previous posts in the community, but couldn’t solve it. For all the methods I have tried, I get correct Salesmen count associated to the buckets as long as I have Product and Sales Person columns in the visual. When I remove the Sales Person column, it sums the Sales Count of all the Salesmen grouped by the Product and segments the Salesmen count under a wrong bucket. Sample Data Product Sales Person Sale Date Sale Count P1 A 2022-08-01 0 P1 A 2022-08-15 0 P1 A 2022-09-01 6 P1 A 2022-09-15 7 P1 A 2022-10-01 8 P1 A 2022-10-15 9 P1 B 2022-08-01 3 P1 B 2022-08-15 11 P1 B 2022-09-01 6 P1 B 2022-09-15 5 P1 B 2022-10-01 10 P1 B 2022-10-15 2 P2 C 2022-08-01 4 P2 C 2022-08-15 3 P2 C 2022-09-01 10 P2 C 2022-09-15 5 P2 C 2022-10-01 7 P2 C 2022-10-15 9 P2 D 2022-08-01 3 P2 D 2022-08-15 20 P2 D 2022-09-01 6 P2 D 2022-09-15 7 P2 D 2022-10-01 1 P2 D 2022-10-15 2 Sales Bucket Min Max No Sales 0 0 1-10 1 10 11-20 11 20 21-30 21 30 31-40 31 40 Desired result Aug No Sales 1-10 11-20 21-30 31-40 P1 1 1 P2 1 1 Aug & Sep No Sales 1-10 11-20 21-30 31-40 P1 1 1 P2 1 1 Aug & Sep & Oct No Sales 1-10 11-20 21-30 31-40 P1 2 P2 2 Any help would be greatly apprecated. Thanks a lot in advance!Solved1.4KViews0likes2Comments
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:00Solved649Views0likes2Comments
Report for Opportunities closing in 0-30 days, 31-60 days, 61-90 days & > 90 days
Hello Everyone, I just started using Power Bi but I have some experience with it, not as much as id like. I built out a dynamics CRM for sales and I decided to make weekly sales reports in Power Bi rather than SSRS-BIDS SQL reporting. I have created a sample page where I have 3 tables stacked on top of each other. It reads like: Opportunities Closing in the Next 30 Days currently shows all opportunities from today's date with [est. close date] "is in the next" 30 days Opportunities Closing in the Next 60 Days currently shows all opportunities from today's date with [est. close date] "is in the next" 60 days shows duplicates of today- day 30, then new after 31-60 Opportuntites Closing in the Next 90 Days currently shows all opportunities from today's date with [est. close date] "is in the next" 90 days shows duplicates of today- day 60, then new after 61-90 I am using the following fields in each table [Topic] [Account] [Est. Close Date] [Pipeline Phase] [Owner] [Date of Last Activity] [Est Revenue] I want each table to show Opportunities without the duplicates. Sort of like binning data. Im looking to display opportunities closing in (0-30)(31-60)(61-90) days not (0-30)(0-60)(0-90) with three different tables. Where do I start? Do I need to do this with code since the filters in power bi don't give me ability to select [est. close date] after X days and before Y days? It seems like this should be relatively easy. Thank you so much for your help. All the best, Josh1.6KViews0likes1Comment
