Weekly Average

[CAWizz]

Hello, 

Any help will be extremely appreciate!

I have the following dataset made of a date column, customerID and group_class colomn with categorical datapoints for classifcation as seen below

app_Date	customer	group_class
20230222	record1	00_0to25999
20230228	record2	00_0to25999
20230207	record3	00_0to25999
20230209	record4	02_30000to34999
20230220	record5	01_26000to29999
20230201	record6	00_0to25999
20230207	record7	00_0to25999
20230204	record8	01_26000to29999
20230226	record9	01_26000to29999
20230208	record10	03_35000to39999
20230210	record11	03_35000to39999
20230207	record12	04_40000to49999
20230203	record13	01_26000to29999
20230228	record14	01_26000to29999
20230204	record15	00_0to25999
20230201	record16	00_0to25999

using the matrix visual, I created a daily distribution view showed as % of row total

To that, I would like to add another column with a calculated field displaying the average of the % for each group as seen below 

Weekly view	01/01/2023	08/01/2023	15/01/2023	22/01/2023	29/01/2023	Average of daily %
00_0to1499	8%	4%				6%
01_1500to1749	12%	14%	14%	14%	16%	14%
02_1750to1999	10%	10%	10%	10%	11%	10%
03_2000to2999	39%	41%	43%	44%	43%	42%
04_3000to4999	24%	24%	27%	25%	24%	25%
05_5000to9999	7%	6%	7%	7%	6%	7%

[CAWizz]

Hello, thank you for looking into this! I used the logic you kindly built for me to calclulate the weekly average and weekly standard deviation (population) however when comparing the PBI mesure outputs to the ones calculated in excel using the same dataset, i end up with some discrepency. I will get back to you as soon as this is fixed one my end! thank you

[v-jingzhang]

Hi @CAWizz 

I guess that your current value field showed as % of row total is based on the count of customerID, right? If so, you can create two measures similar to below, and add only the second measure to the matrix visual. 

Measure = DIVIDE(COUNT('Table (2)'[customer]),CALCULATE(COUNT('Table (2)'[customer]),ALLSELECTED('Table (2)'[category])))

Measure 2 = IF(ISFILTERED('Table (2)'[date]),[Measure],AVERAGEX(VALUES('Table (2)'[date]),[Measure]))

Then in the formatting pane, go to Column subtotals and change the Subtotal label to "Average of daily %". 

You will get the following matrix finally. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.