Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
Solved! Go to Solution.
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!
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!
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
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...
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
34 | |
14 | |
12 | |
12 | |
11 |