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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Average on weeklevel

Goodmorning all,

 

I would like to make a new measurement on weeklevel. On this stage I have costs per day, but i need the average costs per week.

Which DAX-formula do you prefer? I have a filter on datum (date) and week. Thanks in advance!

costs per day.PNG

 

1 ACCEPTED SOLUTION
technolog
Super User
Super User

Hey there! So, if you want to calculate the average costs per week, and you already have costs per day, you can use the DAX formula provided by AlB. The formula he gave you, CALCULATE( AVERAGE(Table1[Costs]), ALL(Table1[Datum]) ), will give you the average costs for the entire table, regardless of the date filter applied. This is because the ALL function removes any filters on the Datum column.

However, if you're saying that when you filter to a specific day, like 3-9-2019, and the cost for that day is 340 euros, then the average also shows 340 euros, it's because the filter context is affecting the calculation. When you filter to a specific day, the average is being calculated only for that day, hence you get the same value as the cost for that day.

To get the average costs for the entire week, even when you filter to a specific day, you need to modify the formula to ignore the day filter but respect the week filter. You can achieve this by using the ALLEXCEPT function. The formula would look something like this:

Measure = CALCULATE( AVERAGE(Table1[Costs]), ALLEXCEPT(Table1, Table1[Week]) )

With this formula, when you filter to a specific day, it will still give you the average costs for the entire week of that day. Hope this helps! If you have any other questions or need further clarification, just let me know!

View solution in original post

3 REPLIES 3
technolog
Super User
Super User

Hey there! So, if you want to calculate the average costs per week, and you already have costs per day, you can use the DAX formula provided by AlB. The formula he gave you, CALCULATE( AVERAGE(Table1[Costs]), ALL(Table1[Datum]) ), will give you the average costs for the entire table, regardless of the date filter applied. This is because the ALL function removes any filters on the Datum column.

However, if you're saying that when you filter to a specific day, like 3-9-2019, and the cost for that day is 340 euros, then the average also shows 340 euros, it's because the filter context is affecting the calculation. When you filter to a specific day, the average is being calculated only for that day, hence you get the same value as the cost for that day.

To get the average costs for the entire week, even when you filter to a specific day, you need to modify the formula to ignore the day filter but respect the week filter. You can achieve this by using the ALLEXCEPT function. The formula would look something like this:

Measure = CALCULATE( AVERAGE(Table1[Costs]), ALLEXCEPT(Table1, Table1[Week]) )

With this formula, when you filter to a specific day, it will still give you the average costs for the entire week of that day. Hope this helps! If you have any other questions or need further clarification, just let me know!

AlB
Super User
Super User

Hi @Anonymous 

If what you are showing is a table visual with Datum and Week in rows:

Measure = CALCULATE( AVERAGE(Table1[Costs]), ALL(Table1[Datum]) ) 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

SU18_powerbi_badge

Anonymous
Not applicable

Thanks for your message! Only one thing I've to say. When I want to compare the costs per day with the average costs per week, I will have the same changes. So when I've got for example 340 euros on 3-9-2019, the average changes to 340. So the average is changing...

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.