Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jorgast
Resolver II
Resolver II

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
Cmcmahan
Resident Rockstar
Resident Rockstar

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])) 

View solution in original post

2 REPLIES 2
Cmcmahan
Resident Rockstar
Resident Rockstar

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])) 

@Cmcmahan 

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

Thats what i needed.

 

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.