Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I have a an attendance table which is tied to a date table. Issue I am having is there isnt data for every single day. I want the 0 days to count e.g.
Mon - 0%
Tue - 0%
Wed - 7%
Thu - 0%
Fri - 0%
The calculation I am trying to do would be 0+0+7+0+0 = 7 divided by 5 = 1 (rounded up)
How do I edit this to include 0 days? The highlighted is the only day in that week that had attendance
Hi @Anonymous
Please try putting "+0" behind your formula
AVG % (M-F) =
VAR WeekdayTable =
FILTER(dDates, dDates[DayOfWeek] <> 6 && dDates[DayOfWeek] <> 0 && dDates[Year] = 2022)
RETURN
AVERAGEX(WeekdayTable, [DailyAttendance]) + 0
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Hey,
That adds 0 at the Year level not the day level
ok, I have overseen something. Can you please try the following:
AVG % (M-F) =
VAR WeekdayTable =
FILTER(dDates, dDates[DayOfWeek] <> 6 && dDates[DayOfWeek] <> 0 && dDates[Year] = 2022)
RETURN
AVERAGEX(WeekdayTable, [DailyAttendance] + 0)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Hey, this seems much closer than what I initially had but for the below calendar week where the highlighted falls, the answer should be 1.4% because Mon, Wed, Thu, and Friday are 0% but Tuesday is 6.6%. Im struggling to see what Im missing 😞
Hi @Anonymous
what really confues me is that you one the one hand speak about weeks but on the other hand you put single dates into the visual and in a previous picture you put year values into a bar chart.
Can you please show the data model and explain the meening of the columns in your table visual?
Best regards
Michael
I created a date table that has all the different dimensions
Can you please sho the definition of the measures you use (e.g. daily attendance or headcount)?
Because I tied my date table to the fBrazilBadge table, the tqble visual only shows dates that have data. I need to be able to show all dates even ones that have no data, that way they are included in the AverageX DAX
Hi @Anonymous
To be honest its really hard to provide a solution in this case. I assume that you have some challenges with the granularity and relations between the tables. Can you maybe provide an PBIX example with dummy data?
Best regards
Michael
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |