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.
I am trying to calculate the Day of Week average by month similar to the image below. The weekdays are totaled up for each month and then the day total is divided by the monthly total. I am able to do this in excel but was trying to do it in PowerBI to help automate the process. I am able to get the data into PowerBI and can get it to work if I filter to a single month and using % of Column total, but this falls apart when trying to use a matrix.
Is this possible in PowerBI?
Sample Data:
AccrualDate AccrualDoW AccrualMonth Item Total 2019-01-01 Tuesday January A 19345.56 2019-01-01 Tuesday January B 12345.37 2019-01-02 Wednesday January A 38433.45 2019-01-02 Wednesday January B 37567.32 2019-01-03 Thursday January A 34853.34 2019-01-03 Thursday January B 78893.87 2019-01-04 Friday January A 43873.46 2019-01-04 Friday January B 34853.34
2019-02-03 Monday February A 33457.32
2019-02-03 Monday February B 78277.56
2019-02-03 Tuesday February A 93847.32
2019-02-03 Tuesday February B 67632.98
2019-02-03 Wednesday February A 38447.72
2019-02-03 Wednesday February B 44745.02
2019-02-03 Thursday February A 23474.52
2019-02-03 Thursday February B 37475.12
2019-02-03 Friday February A 63475.92
2019-02-03 Friday February B 72975.43 * this continues for everyday of the week.
Hi,
To answer your question, yes this is possible.
Robbe
Any ideas on what approach I should take to get this to work in PowerBI?
Some sample data would help us help you a long way! 🙂
@RobbeVL I appologize for not attaching some sample data.
The SQL query returns data in the format below:
AccrualDate AccrualDoW AccrualMonth Item Total 2019-01-01 Tuesday January A 19345.56
2019-01-01 Tuesday January B 12345.37
2019-01-02 Wednesday January A 38433.45
2019-01-02 Wednesday January B 37567.32
2019-01-03 Thursday January A 34853.34
2019-01-03 Thursday January B 78893.87
2019-01-04 Friday January A 43873.46
2019-01-04 Friday January B 34853.34
* this continues for everyday of the week.
For starters I was just going to try and use the WeekDay Total for both Items, and then eventually have other columns showing Item A, and Item B's WeekDay average
With the data you gave me I was able to produce this matrix table, without using any measure.
Does this make sense to you ?
@RobbeVL I have updated the Sample data to include another month, and inserted it into the original post.
Thanks again for looking into this.
@RobbeVL I could also get it to work for a single month, but when it gets into the next month of February, the % of column total would not work anymore. I didnt continue the sample data into another month, but it is continuous.
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 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |