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 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 |
Hi @growandgrow ,
Not very clear, Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
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 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |