Forum Discussion
Weekly Average
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% |
2 Replies
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.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
