Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I have a problem of averaging according to a context. I have a data model with a fact table and a "Date" dimension table
Indeed, I want to calculate the average based on the month selected to calculate the average over the last 12 months (12 rolling months). Look at the following table:
13/10/2019 | 6 |
21/10/2019 | 3 |
01/09/2019 | 15 |
14/09/2019 | 1 |
20/11/2019 | 3 |
27/11/2019 | 3 |
29/11/2019 | 6 |
30/11/2019 | 1 |
Par exemple :
- For example, the average I want to have when I select September is: (15 + 1)/ 1= 16
- For example, the average I want to have when I select October is: (16 + 9)/2 = 12,5
- For example, the average I want to have when I select November is: (16 + 9+13)/3 = 12,66
For information, the relationship between my fact table and the "Date" dimension is based on a "Date" field.
Solved! Go to Solution.
Hi @Pedro77000 ,
We can create a measure as below.
Measure =
VAR SUMA =
CALCULATE (
SUM ( 'Table'[value] ),
FILTER ( ALL ( 'Table' ), 'Table'[date] <= MAX ( 'date'[Date] ) )
)
VAR COUNTM =
CALCULATE (
DISTINCTCOUNT ( 'Table'[YearMOnth] ),
FILTER ( ALL ( 'Table' ), 'Table'[date] <= MAX ( 'date'[Date] ) )
)
RETURN
DIVIDE ( SUMA, COUNTM )
Also you can find the pbix as attached.
Hi @Pedro77000 ,
To use ALLEXCEPT instead of ALL should work.
FILTER ( ALLEXCEPT ( 'Table','Table'[region] ), 'Table'[date] <= MAX ( 'date'[Date] ) )
If it doesn't meet your requirement, Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Hi @Pedro77000 ,
We can create a measure as below.
Measure =
VAR SUMA =
CALCULATE (
SUM ( 'Table'[value] ),
FILTER ( ALL ( 'Table' ), 'Table'[date] <= MAX ( 'date'[Date] ) )
)
VAR COUNTM =
CALCULATE (
DISTINCTCOUNT ( 'Table'[YearMOnth] ),
FILTER ( ALL ( 'Table' ), 'Table'[date] <= MAX ( 'date'[Date] ) )
)
RETURN
DIVIDE ( SUMA, COUNTM )
Also you can find the pbix as attached.
Good morning v-frfei-msft !!
Thank you very much for this reply.
The solution works well. But this average I cannot have it by region, I believe because of the filter ALL.
The average for the total is good but when I split by region, it's the same overall average that appears in front of each region.
Hi @Pedro77000 ,
To use ALLEXCEPT instead of ALL should work.
FILTER ( ALLEXCEPT ( 'Table','Table'[region] ), 'Table'[date] <= MAX ( 'date'[Date] ) )
If it doesn't meet your requirement, Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Thank you so much Franck ! that works well ! even if the disadvantage of ALLEXCEPT is having to enter the fields of all dimension tables.
Hi @Pedro77000
Try changing ALL to ALLSELECTED and then it should carry your filters through.
---
Please hit the "Accept as Solution" button if my post answered your question! If my post was helpful please consider giving it a "Thumbs Up."
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |