Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi All,
I have a report which has an activity date range.
However I was to create a visual which tells me of the number of positions raised by an indivdual in a quarter what is the weekly average number based on the number of weeks passed in a given quarter.
Below is a list of the table I have which tells me the total number of positions raised by staff for the month of April (4) and May - So far (5).
I have quite a robust date table which tells me the activity date and the week number, month number, quarter number and year for each activity date although it is too big to paste in here.
Raw Data:
Staff | 4 | 5 | Total |
Annu.Bhutta | 0 | 37 | 37 |
Eilysh.Websdale | 35 | 10 | 45 |
Hannah.Lelliott | 40 | 25 | 65 |
Javeria.Khan | 0 | 29 | 29 |
Jovette.Daniel | 12 | 27 | 39 |
Koralee.Swete | 8 | 24 | 32 |
Lauren.Haddow | 58 | 43 | 101 |
Marta.Noskova | 3 | 6 | 9 |
Nicole.Henderson | 14 | 16 | 30 |
Olivia.Simunec | 26 | 68 | 94 |
Ori.Newby | 22 | 26 | 48 |
Rafia.Khan | 0 | 0 | 0 |
Sabrina.Benchaib | 32 | 33 | 65 |
Sehr.Aslam | 0 | 0 | 0 |
Sheridan.Gerrard | 57 | 47 | 104 |
Stephanie.Gainsford | 19 | 82 | 101 |
Valentina.Loch | 19 | 12 | 31 |
Verena.Braun1 | 74 | 82 | 156 |
Grand Total | 423 | 572 | 986 |
Calendar Table:
Week Number | Quarter Number | Year | Week QTR Count |
1 | 1 | 2022 | 1 |
2 | 1 | 2022 | 2 |
3 | 1 | 2022 | 3 |
4 | 1 | 2022 | 4 |
5 | 1 | 2022 | 5 |
6 | 1 | 2022 | 6 |
7 | 1 | 2022 | 7 |
8 | 1 | 2022 | 8 |
9 | 1 | 2022 | 9 |
10 | 1 | 2022 | 10 |
11 | 1 | 2022 | 11 |
12 | 1 | 2022 | 12 |
13 | 1 | 2022 | 13 |
14 | 2 | 2022 | 1 |
15 | 2 | 2022 | 2 |
16 | 2 | 2022 | 3 |
17 | 2 | 2022 | 4 |
18 | 2 | 2022 | 5 |
19 | 2 | 2022 | 6 |
20 | 2 | 2022 | 7 |
21 | 2 | 2022 | 8 |
22 | 2 | 2022 | 9 |
23 | 2 | 2022 | 10 |
24 | 2 | 2022 | 11 |
25 | 2 | 2022 | 12 |
26 | 2 | 2022 | 13 |
27 | 3 | 2022 | 1 |
28 | 3 | 2022 | 2 |
29 | 3 | 2022 | 3 |
30 | 3 | 2022 | 4 |
31 | 3 | 2022 | 5 |
32 | 3 | 2022 | 6 |
33 | 3 | 2022 | 7 |
34 | 3 | 2022 | 8 |
35 | 3 | 2022 | 9 |
36 | 3 | 2022 | 10 |
37 | 3 | 2022 | 11 |
38 | 3 | 2022 | 12 |
39 | 3 | 2022 | 13 |
40 | 4 | 2022 | 1 |
41 | 4 | 2022 | 2 |
42 | 4 | 2022 | 3 |
43 | 4 | 2022 | 4 |
44 | 4 | 2022 | 5 |
45 | 4 | 2022 | 6 |
46 | 4 | 2022 | 7 |
47 | 4 | 2022 | 8 |
48 | 4 | 2022 | 9 |
49 | 4 | 2022 | 10 |
50 | 4 | 2022 | 11 |
51 | 4 | 2022 | 12 |
52 | 4 | 2022 | 13 |
I would like to achieve something like the below which tells me how many weeks are we into the current quarter and then divide the total number of positions raised in the current quarter by the number of weeks in this case 8.
Staff | 4 | 5 | Total | Weeks Into Quarter | Avg PW |
Annu.Bhutta | 0 | 37 | 37 | 8 | 4.625 |
Eilysh.Websdale | 35 | 10 | 45 | 8 | 5.625 |
Hannah.Lelliott | 40 | 25 | 65 | 8 | 8.125 |
Javeria.Khan | 0 | 29 | 29 | 8 | 3.625 |
Jovette.Daniel | 12 | 27 | 39 | 8 | 4.875 |
Koralee.Swete | 8 | 24 | 32 | 8 | 4 |
Lauren.Haddow | 58 | 43 | 101 | 8 | 12.625 |
Marta.Noskova | 3 | 6 | 9 | 8 | 1.125 |
Nicole.Henderson | 14 | 16 | 30 | 8 | 3.75 |
Olivia.Simunec | 26 | 68 | 94 | 8 | 11.75 |
Ori.Newby | 22 | 26 | 48 | 8 | 6 |
Rafia.Khan | 0 | 0 | 0 | 8 | 0 |
Sabrina.Benchaib | 32 | 33 | 65 | 8 | 8.125 |
Sehr.Aslam | 0 | 0 | 0 | 8 | 0 |
Sheridan.Gerrard | 57 | 47 | 104 | 8 | 13 |
Stephanie.Gainsford | 19 | 82 | 101 | 8 | 12.625 |
Valentina.Loch | 19 | 12 | 31 | 8 | 3.875 |
Verena.Braun1 | 74 | 82 | 156 | 8 | 19.5 |
Grand Total | 423 | 572 | 986 | 8 | 123.25 |
Does anyone know how I can calculate this?
Thanks,
Tom
Solved! Go to Solution.
I have managed to do a temporary fix for this but if anyone knows a permanent fix until then here it is.
I have a relation from table 1 to my date table.
I added a slicer for activity date to the slicer which let me select a specific range I wanted to report on
EG - 04/04/2022 - 25/05/2022, which is 8 financial weeks
I then created 3 measure's.
1 - Min Date Range =
I have managed to do a temporary fix for this but if anyone knows a permanent fix until then here it is.
I have a relation from table 1 to my date table.
I added a slicer for activity date to the slicer which let me select a specific range I wanted to report on
EG - 04/04/2022 - 25/05/2022, which is 8 financial weeks
I then created 3 measure's.
1 - Min Date Range =
Hi,
Share the download link of your PBI file. Please also ensure that you have a Calendar Table with a Weeknumber column in the Calendar Table.
Hi Ashish,
I'm not able to provide a download link for you PBIx because my organisation doesn't allow us to share/export this.
I have updated my topic thread however with the table I am using. My date table does have a week number column associated with it as well as month, quarter and year
Hi,
Is the first table in your Original post, the raw data table? Why do you not have a Date column? Also, please share a Calendar table with a week number column (which restarts) at the beginning of each quarter.
Hi Ashish,
The first table is my raw data, I have just updated the original post with a calendar table.
Note I don't have a Week Qtr Count column in my actual calendar table yet though but I can create a custom column to add this in.
Hi @Thomas-B-Hudson ,
Based on just the information provided, you can simply create a measure that returns a division an example is
MyMeasure =
DIVIDE ( SUM ( Table[Column1] ), SUM ( Table[Column2] ) )
If this doesn't answer your query, please refer to this link on how the community can help you better: https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071...
Proud to be a Super User!
User | Count |
---|---|
120 | |
72 | |
69 | |
57 | |
50 |
User | Count |
---|---|
167 | |
82 | |
68 | |
65 | |
53 |