cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jorgast Member
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
 
WeekNumSales AreaSales_OfferedYear
Week 18Area 130002018
Week 18Area 240002018
Week 18Area 37002018
Week 18Area 420002018
Week 19Area 120002018
Week 19Area 26002018
Week 19Area 322002018
Week 19Area 490002018
Week 20Area 140002018
Week 20Area 26002018
Week 20Area 330002018
Week 20Area 490002018
Week 21Area 135002018
Week 21Area 27002018
Week 21Area 330002018
Week 21Area 490002018
Week 22Area 135002018
Week 22Area 26002018
Week 22Area 340002018
Week 22Area 480002018
Week 18Area 150002019
Week 18Area 250002019
Week 18Area 330002019
Week 18Area 430002019
Week 19Area 150002019
Week 19Area 24002019
Week 19Area 330002019
Week 19Area 430002019
Week 20Area 132002019
Week 20Area 25002019
Week 20Area 331002019
Week 20Area 430002019
Week 21Area 150002019
Week 21Area 25002019
Week 21Area 330002019
Week 21Area 433002019
Week 22Area 140002019
Week 22Area 25002019
Week 22Area 330002019
Week 22Area 430002019
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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
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
Jorgast Member
Member

Re: Division in a Measure

@Cmcmahan 

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

Thats what i needed.

 

Thanks

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 374 members 4,195 guests
Please welcome our newest community members: