topic Re: Average on weeklevel in DAX Commands and Tips

Average on weeklevel

Anonymous — Tue, 19 May 2020 07:00:20 GMT

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!

Re: Average on weeklevel

AlB — Tue, 19 May 2020 07:18:13 GMT

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

Re: Average on weeklevel

Anonymous — Tue, 19 May 2020 07:40:32 GMT

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...

Re: Average on weeklevel

technolog — Sun, 17 Sep 2023 17:40:43 GMT

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!