Forum Discussion
Average granularity
Hi, i have a question iif i have a fact tabel, over a copupe of years, but not all years have all months ,
so example may start Feb to Oct.
if i want an average total / 12 if so I have 3 measures ;
Tunits:=SUMX(Table1,Table1[Units])
AvgXYM:=DIVIDE( [Tunits], 12)
Avgx:=AVERAGEX(VALUES('Calendar'[MMM-YYYY]),[Tunits])
where i just divide this works, but I don't know way the averagex does not, the 'calendar' has all the months, yes still
gives an average for number in Table1 ? , so why ? and can I make it 'see' all the months in the calendar table? }
This is being displayed in a Year only pivot table.
Richard.
Hi Dicken ,
May I ask if the provided solution helped in resolving the issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you!!
5 Replies
Hi,
Since you do not have data for all the months shown in the Calendar table, the average will only be for the months for which you have data in the Fact table. You should just use the first 2 measures. Also, simplify the first measure to
=sum(Table1[units])
I think an approach, and I have not got this working , it to use parallelperiod, so
EVALUATE SELECTCOLUMNS( PARALLELPERIOD( 'Calendar'[Date],0,YEAR), "N", FORMAT( 'Calendar'[Date],"MMM-YYYY") )would give a single row table to iterate over and would conatain all dates, just an idea.
Hi
First, you don't need SUMX for this. Just go for regular SUM. Use SUMX if you want a calculation that SUM doesn't suport like Total Price = SUMX ( tbl, tbl[units] * tbl[unit sales price] )
Tunits:=SUMX(Table1,Table1[Units])AVERAGEX may not be producing the expected result because it iterates through all visible distinct MMM-YYYY values. The number of unique values could be less than, equal to, or greater than 12. Additionally, your DIVIDE measure uses a constant value of 12. It's important to note that AVERAGEX will exclude any MMM-YYYY that lacks a value. For instance, if Apr-2025 has no value, the average will be computed based only on the remaining months. So, even if 12 months are currently visible, the actual calculation will be based on 11.
Hi Dicken ,
May I ask if the provided solution helped in resolving the issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you!!
Not really as I wanted a way to force it to include all months present in the calendar ,
reagardless of whether that month had a value in the fact table.
