Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi All,
I have a table like this
Countries | Cities | Years | Temperature |
Country1 | City1 | Year1 | Temperature(value) |
Country1 | City1 | Year2 | Temperature(value) |
Country1 | City1 | Year3 | Temperature(value) |
Country1 | City2 | Year1 | Temperature(value) |
Country1 | City2 | Year2 | Temperature(value) |
Country1 | City2 | Year3 | Temperature(value) |
Country2 | City3 | Year1 | Temperature(value) |
Country2 | City3 | Year2 | Temperature(value) |
Country2 | City3 | Year3 | Temperature(value) |
Country2 | City4 | Year1 | Temperature(value) |
I would like to calculate the moving average temperature by City and Country. All of my previous attempts have slipped through the Country/City groups meaning that moving averages contained data from previous cities and years.
Is there any solution that somehow strictly calculates inside the boundaries?
The intended solution would look like this for a 2-year moving average
Countries | Cities | Years | Temperature | Temp Moving Average |
Country1 | City1 | Year1 | Temperature(value) | - |
Country1 | City1 | Year2 | Temperature(value) | M.AVG1 |
Country1 | City1 | Year3 | Temperature(value) | M.AVG2 |
Country1 | City2 | Year1 | Temperature(value) | - |
Country1 | City2 | Year2 | Temperature(value) | M.AVG1 |
Country1 | City2 | Year3 | Temperature(value) | M.AVG2 |
Country2 | City3 | Year1 | Temperature(value) | - |
Country2 | City3 | Year2 | Temperature(value) | M.AVG1 |
Country2 | City3 | Year3 | Temperature(value) | M.AVG2 |
Country2 | City4 | Year1 | Temperature(value) | - |
I would be very grateful even if someone only pointed out the problem or directed me in the right direction.
Solved! Go to Solution.
Hi @Anonymous
Please try
Temp Moving Average =
CALCULATE (
AVERAGE ( TableName[Temperature] ),
ALLEXCEPT ( TableName, TableName[Countries], TableName[Cities] ),
TableName[Years] <= MAX ( TableName[Years] )
)
Hi @Anonymous
Please try
Temp Moving Average =
CALCULATE (
AVERAGE ( TableName[Temperature] ),
ALLEXCEPT ( TableName, TableName[Countries], TableName[Cities] ),
TableName[Years] <= MAX ( TableName[Years] )
)
User | Count |
---|---|
17 | |
17 | |
14 | |
13 | |
13 |
User | Count |
---|---|
17 | |
14 | |
14 | |
10 | |
8 |