cancel
Showing results for
Did you mean:
Member

## Division in a Measure

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?

Date Table
WeekNum = COMBINEVALUES(" ", "Week" ,WEEKNUM(Dates[Date],1))

Sales Table
Count of WeekNum= DISTINCTCOUNT(Dates[WeekNum])
Avg Sales2018 = CALCULATE(sum('Sample Data'[Sales_Offered]), "2018", ALLEXCEPT(Dates, Dates[Date])) / 5
Avg Sales2019 = CALCULATE(sum('Sample Data'[Sales_Offered]), "2019", ALLEXCEPT(Dates, Dates[Date])) / 5

Sample Data

 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
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Division in a Measure

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])) `
2 REPLIES 2
Super User

## Re: Division in a Measure

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])) `
Highlighted
Member

## Re: Division in a Measure

Perfect...I didnt think to move the division before the filter.

Thats what i needed.

Thanks

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 374 members 4,195 guests
Recent signins: