Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello BI Community,
I am trying to get an average of sales offered for 2018 and 2019 by week and have that average be constant in a line/bar chart. I have a date table setup where i have the week number broken out. I have a measure, in my sales table, that does a distinct count of WeekNum. I have a measure that calculates the average sales. When i put in the "Count of WeekNum" i am getting the Sum of sales. When i hardcode the "Count of WeekNum" then i am getting the data i want. Am i missing something in measure that calcualtes the average using another measure in the denominator?
WeekNum | Sales Area | Sales_Offered | Year |
Week 18 | Area 1 | 3000 | 2018 |
Week 18 | Area 2 | 4000 | 2018 |
Week 18 | Area 3 | 700 | 2018 |
Week 18 | Area 4 | 2000 | 2018 |
Week 19 | Area 1 | 2000 | 2018 |
Week 19 | Area 2 | 600 | 2018 |
Week 19 | Area 3 | 2200 | 2018 |
Week 19 | Area 4 | 9000 | 2018 |
Week 20 | Area 1 | 4000 | 2018 |
Week 20 | Area 2 | 600 | 2018 |
Week 20 | Area 3 | 3000 | 2018 |
Week 20 | Area 4 | 9000 | 2018 |
Week 21 | Area 1 | 3500 | 2018 |
Week 21 | Area 2 | 700 | 2018 |
Week 21 | Area 3 | 3000 | 2018 |
Week 21 | Area 4 | 9000 | 2018 |
Week 22 | Area 1 | 3500 | 2018 |
Week 22 | Area 2 | 600 | 2018 |
Week 22 | Area 3 | 4000 | 2018 |
Week 22 | Area 4 | 8000 | 2018 |
Week 18 | Area 1 | 5000 | 2019 |
Week 18 | Area 2 | 5000 | 2019 |
Week 18 | Area 3 | 3000 | 2019 |
Week 18 | Area 4 | 3000 | 2019 |
Week 19 | Area 1 | 5000 | 2019 |
Week 19 | Area 2 | 400 | 2019 |
Week 19 | Area 3 | 3000 | 2019 |
Week 19 | Area 4 | 3000 | 2019 |
Week 20 | Area 1 | 3200 | 2019 |
Week 20 | Area 2 | 500 | 2019 |
Week 20 | Area 3 | 3100 | 2019 |
Week 20 | Area 4 | 3000 | 2019 |
Week 21 | Area 1 | 5000 | 2019 |
Week 21 | Area 2 | 500 | 2019 |
Week 21 | Area 3 | 3000 | 2019 |
Week 21 | Area 4 | 3300 | 2019 |
Week 22 | Area 1 | 4000 | 2019 |
Week 22 | Area 2 | 500 | 2019 |
Week 22 | Area 3 | 3000 | 2019 |
Week 22 | Area 4 | 3000 | 2019 |
Solved! Go to Solution.
It looks like you're getting the correct numerator value because you're using the ALLEXCEPT to change the filter context, but you're not doing the same for the denominator.
Try this so that you're applying the same context changes to both halves of the equation:
Avg Sales2018 = CALCULATE(sum('Sample Data'[Sales_Offered])/[Count of WeekNum], "2018", ALLEXCEPT(Dates, Dates[Date]))
It looks like you're getting the correct numerator value because you're using the ALLEXCEPT to change the filter context, but you're not doing the same for the denominator.
Try this so that you're applying the same context changes to both halves of the equation:
Avg Sales2018 = CALCULATE(sum('Sample Data'[Sales_Offered])/[Count of WeekNum], "2018", ALLEXCEPT(Dates, Dates[Date]))
Perfect...I didnt think to move the division before the filter.
Thats what i needed.
Thanks
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
104 | |
94 | |
76 | |
62 | |
59 |