The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
Solved! Go to Solution.
Hi @SecretChimpanze ,
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
Hi @SecretChimpanze ,
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
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-...
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/1447...