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.
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
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |