Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
We have a column which displays bins.
$0.00 - $0.49
$0.50-$0.99
1. If you look at the pic below. For the bins $9.50 to $9.99 it has different values under "Average price test" for every week ending date. We want to average all the average price test values generated among the same bins during different week ending dates and display only one value for the bins $9.50 to $9.99. Also, since it is repeated 9 times. As per the image below. We created a column Weeks. It should display 9
2- Similary, lets says for a selected time period. I select 2022, There would be a list of averages for different bins on different week ending date. Now, a average of 22.79 is generated between bins $11.00-%11.49 . Instead of display the bin $11.00-%11.49, the out put should look line Max - Regular Price, similarly the smallest average should dispay Min -Regular Price under bins instead of bins for max and min regular price.
unit_price_bins | Average Price _Test | week_ending_date |
$9.50 to $9.99 | 9.912071362 | 11-01-2020 00:00 |
$9.50 to $9.99 | 9.967245886 | 18-01-2020 00:00 |
$9.50 to $9.99 | 9.835462594 | 25-01-2020 00:00 |
$9.50 to $9.99 | 9.943143498 | 01-02-2020 00:00 |
$9.50 to $9.99 | 9.9222103 | 08-02-2020 00:00 |
$9.50 to $9.99 | 9.981874181 | 15-02-2020 00:00 |
$9.50 to $9.99 | 9.904210054 | 22-02-2020 00:00 |
$9.50 to $9.99 | 9.986268657 | 29-02-2020 00:00 |
$9.50 to $9.99 | 9.927839041 | 07-03-2020 00:00 |
$9.00 to $9.49 | 9.201978812 | 14-03-2020 00:00 |
$9.00 to $9.49 | 9.093813953 | 21-03-2020 00:00 |
$9.00 to $9.49 | 9.078584832 | 28-03-2020 00:00 |
$8.50 to $8.99 | 8.960496217 | 04-04-2020 00:00 |
$9.50 to $9.99 | 9.980544218 | 11-04-2020 00:00 |
$9.50 to $9.99 | 9.855549925 | 18-04-2020 00:00 |
$9.50 to $9.99 | 9.934844875 | 25-04-2020 00:00 |
$9.50 to $9.99 | 9.904888187 | 02-05-2020 00:00 |
$9.50 to $9.99 | 9.602547016 | 09-05-2020 00:00 |
$9.00 to $9.49 | 9.375084497 | 16-05-2020 00:00 |
$9.50 to $9.99 | 9.77926479 | 23-05-2020 00:00 |
$9.50 to $9.99 | 9.99 | 30-05-2020 00:00 |
$9.50 to $9.99 | 9.99 | 06-06-2020 00:00 |
$9.50 to $9.99 | 9.954360418 | 13-06-2020 00:00 |
$9.50 to $9.99 | 9.99 | 20-06-2020 00:00 |
$9.50 to $9.99 | 9.99 | 27-06-2020 00:00 |
$9.50 to $9.99 | 9.979146635 | 04-07-2020 00:00 |
$9.50 to $9.99 | 9.980607577 | 11-07-2020 00:00 |
$9.50 to $9.99 | 9.901129389 | 18-07-2020 00:00 |
$9.50 to $9.99 | 9.863962401 | 25-07-2020 00:00 |
$9.50 to $9.99 | 9.919057164 | 01-08-2020 00:00 |
$9.50 to $9.99 | 9.918323864 | 08-08-2020 00:00 |
$9.50 to $9.99 | 9.981450704 | 15-08-2020 00:00 |
$9.50 to $9.99 | 9.99 | 22-08-2020 00:00 |
$9.50 to $9.99 | 9.984128508 | 29-08-2020 00:00 |
$9.50 to $9.99 | 9.980865485 | 05-09-2020 00:00 |
$9.50 to $9.99 | 9.977431694 | 12-09-2020 00:00 |
$9.00 to $9.49 | 9.115331901 | 19-09-2020 00:00 |
$8.50 to $8.99 | 8.946884354 | 26-09-2020 00:00 |
$8.50 to $8.99 | 8.935139412 | 03-10-2020 00:00 |
$9.00 to $9.49 | 9.016981481 | 10-10-2020 00:00 |
$9.00 to $9.49 | 9.402101266 | 17-10-2020 00:00 |
$9.50 to $9.99 | 9.95279918 | 24-10-2020 00:00 |
$9.50 to $9.99 | 9.964530516 | 31-10-2020 00:00 |
$9.50 to $9.99 | 9.97065371 | 07-11-2020 00:00 |
$9.50 to $9.99 | 9.95548951 | 14-11-2020 00:00 |
$9.50 to $9.99 | 9.90391198 | 21-11-2020 00:00 |
$9.50 to $9.99 | 9.99 | 28-11-2020 00:00 |
$9.50 to $9.99 | 9.99 | 05-12-2020 00:00 |
$9.50 to $9.99 | 9.982562225 | 12-12-2020 00:00 |
$9.50 to $9.99 | 9.99 | 19-12-2020 00:00 |
$9.50 to $9.99 | 9.99 | 26-12-2020 00:00 |
$9.50 to $9.99 | 9.99 | 02-01-2021 00:00 |
Formulae we are currently using: Could you correct/ incorrect.
@puneethjp , seem like you need bucket on a measure. For that you need a table with range and use that in another measure grouped by Week and using the bucket min and max range
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k
User | Count |
---|---|
95 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |