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.
I am doing some training work in AdventureWorksDW database.
I have two measures:
Sum of SalesAmount = SUM(FactInternetSales[SalesAmount])
MonthlyAverageSales = AVERAGEX (
VALUES(DimDate[EnglishMonthName]),
[Sum of SalesAmount]
)
I have developed the above report, choosing only two years 2006 and 2007, and only 4 months using slicers.
While the measure [Sum of SalesAmount] shows the correct value, the other measure [MonthlyAverageSales] shows incorrect data in the Total row; however, both measures are right, when aggregated for each year 2006 and 2007.
The measure [MonthlyAverageSales] at the Total row should actually be $ 5,916,696.73/8, which is $ 739,587.09. (8 in denominator represents 8 months - Jan,Feb,Nov,Dec for each year - 2006 and 2007)
Instead, what I see is $ 5,916,696.73/4, which is $1,479,174.18; (4 in denominator represents 4 months- Jan,Feb,Nov,Dec, combining both years together)
Now, I have changed the formula, and have created a new measure:
MonthlyAverageCorrect = AVERAGEX (
KEEPFILTERS(VALUES(DimDate[EnglishMonthName])),
[Sum of SalesAmount]
)
I still do not see any change. Instead of getting $ 739,587.09, I still see $1,479,174.18.
Where am I in error ?
Solved! Go to Solution.
You could try this:
Avg =
AVERAGEX (
KEEPFILTERS (
FILTER (
ALL ( DimDate[EnglishMonthName], DimDate[CalendarYear] ),
[Sum of SalesAmount] > 0
)
),
[Sum of SalesAmount]
)
or
Avg CROSSFILTER =
AVERAGEX (
CROSSJOIN (
DISTINCT ( DimDate[EnglishMonthName] ),
DISTINCT ( DimDate[CalendarYear] )
),
[Sum of SalesAmount]
)
or
Avg SUMMARIZE=
AVERAGEX (
SUMMARIZE ( DimDate, DimDate[EnglishMonthName], DimDate[CalendarYear] ),
[Sum of SalesAmount]
)
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |