Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
82 | |
74 | |
66 |
User | Count |
---|---|
121 | |
105 | |
102 | |
82 | |
72 |