To calculate average of value based on weeks

[puneethjp]

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. 

Max Regular Price =

VAR MaxAveragePrice =

    MAXX(

        all(Dim_Date),

        [Average Price]

    )

RETURN

    CALCULATE(

        MaxAveragePrice,

        FILTER(

            v_us_rgm_p_promo_scorecard_1,

            v_us_rgm_p_promo_scorecard_1[promoted_flag] = "non promoted"

        )

    )

Min Regular Price =

VAR MinAveragePrice =

    MINX(

        all(Dim_Date),

        [Average Price]

    )

RETURN

    CALCULATE(

        MinAveragePrice,

        FILTER(

            v_us_rgm_p_promo_scorecard_1,

            v_us_rgm_p_promo_scorecard_1[promoted_flag] = "non promoted"

        )

    )

Average Price _Test =

var a = [Dollar Period]

 var b = [Units Period]

 var c = DIVIDE(a,b)

 var d = SUMX(VALUES(v_us_rgm_p_promo_scorecard_1[unit_price_bins]),c)

 return

 SWITCH(TRUE(),

 SELECTEDVALUE(v_us_rgm_p_promo_scorecard_1[unit_price_bins]) = "Max-Regular Price",[Max Regular Price],

 SELECTEDVALUE(v_us_rgm_p_promo_scorecard_1[unit_price_bins])= "Min-Regular Price",[Min Regular Price] ,c)

Appreciate your time and help. 

Thanks 

Puneeth

[amitchandak]

@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