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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors