Forum Discussion
Six Week Average Appearing in each Row
Hi all,
I work in the retail space and have a matrix that displays "region" number and "district" number and "store" number as my rows.
In the columns portion of my matrix, I have the fiscal period.
In the values portion of my matrix, I have a published on time calculation that is working correctly.
What I have been asked to do is place two additional columns (as rows) next to "store" for a 6 week average and a YTD average.
I have a "6_Wk_Avg" column pulling in only the values for the last 6 weeks.
I have a "ytd_avg" column pulling in only the values for YTD.
I have a "6WEEK COUNTER" column placing a 1 in cells that are in the last 6 weeks.
I have a "YTD COUNTER" column placing a 1 in cells that are in the YTD.
I was thinking of taking the SUM of 6_Wk_Avg / SUM of "6WEEK COUNTER" in power query to get a value I could use as a row since all values would be the same at the store level. A snapshot of my data is below - does anyone have any experience in providing SUM numbers before value calculations in a Power BI matrix?
| Region | District | Store | Week Start Date | Published On Time | Pre Publication Edits | BO_Wk | Source_BO_Wk | Source_BO_Wk Minus 5 | Today | Published On Time - Number | 6_Wk_Avg | ytd_avg | Week Number | Count | YTD COUNTER | 6 WEEK COUNTER |
| 2 | 8577 | 2598 | 5/8/2022 | 1 | 98 | 202220 | 202220 | 202215 | 5/4/2022 | 1 | 1 | 1 | 20 | 1 | 1 | 1 |
| 10 | 8671 | 2601 | 5/8/2022 | 1 | 0 | 202220 | 202220 | 202215 | 5/4/2022 | 1 | 1 | 1 | 20 | 1 | 1 | 1 |
| 2 | 8658 | 2603 | 5/8/2022 | 1 | 23 | 202220 | 202220 | 202215 | 5/4/2022 | 1 | 1 | 1 | 20 | 1 | 1 | 1 |
| 12 | 8548 | 2604 | 5/8/2022 | 1 | 133 | 202220 | 202220 | 202215 | 5/4/2022 | 1 | 1 | 1 | 20 | 1 | 1 | 1 |
| 10 | 8665 | 2605 | 5/8/2022 | 1 | 254 | 202220 | 202220 | 202215 | 5/4/2022 | 1 | 1 | 1 | 20 | 1 | 1 | 1 |
| 8 | 8631 | 2617 | 5/8/2022 | 1 | 63 | 202220 | 202220 | 202215 | 5/4/2022 | 1 | 1 | 1 | 20 | 1 | 1 | 1 |
| 10 | 8655 | 2619 | 5/8/2022 | 1 | 59 | 202220 | 202220 | 202215 | 5/4/2022 | 1 | 1 | 1 | 20 | 1 | 1 | 1 |
1 Reply
Hei_aQingHelper I
Hi Anonymous ,
Not very clear, Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.