topic Re: Calculated measure to get an average with filters in DAX Commands and Tips

Calculated measure to get an average with filters

Anonymous — Thu, 22 Feb 2024 23:35:29 GMT

Hi,

I am looking for a solution to get the average of historical data based on a multiple criteria.

Example of what I would use if I were doing it in Excel:

=AVERAGEIFS(average_range,criteria_range_1,"Actual",criteria_range_2,"Week x")

Week x would be either Week 0, Week 1 or Week 2

Launch Week	Week Ending	Actual/Forecast	Sales Volume
Week 0	2-Jun-23	Actual	1000
Week 1	9-Jun-23	Actual	1500
Week 2	16-Jun-23	Actual	2000
	23-Jun-23	Actual	800
	30-Jun-23	Actual	800
	7-Jul-23	Actual	800
Week 0	14-Jul-23	Actual	3000
Week 1	21-Jul-23	Actual	3500
Week 2	28-Jul-23	Actual	4000
	4-Aug-23	Actual	800
	11-Aug-23	Actual	800
	18-Aug-23	Actual	800
Week 0	25-Aug-23	Forecast	2000
Week 1	1-Sep-23	Forecast	2500
Week 2	8-Sep-23	Forecast	3000

Re: Calculated measure to get an average with filters

lbendlin — Thu, 22 Feb 2024 22:55:24 GMT

You talk about prior years but your sample data does not reflect that. In addition, time intelligence functions have no concept of "week". That is something you need to bring to the table yourself.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Calculated measure to get an average with filters

Anonymous — Fri, 23 Feb 2024 08:20:23 GMT

Hi Anonymous ，

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

Create a measure

Average Sales Volume = CALCULATE( AVERAGE(SalesData[Sales Volume]), FILTER( SalesData, SalesData[Actual/Forecast] = "Actual" && (SalesData[Launch Week] = "Week 0" || SalesData[Launch Week]= "Week 1" || SalesData[Launch Week] = "Week 2") ) )

Final output

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly