- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Month plus month average
Hi,
I have a table in Power Bi with all our staff, and costings. The tsblr has a "Date" column, as well as "Year", "Month" & "Quarter" and then a "Cost" column.
I have been looking for a way to calculate the average "Cost" in a specific way but unable to find an answer.
What I need, is to sum for each month (for monthly cost totals)
Then
Average between month 1 & 2,
Average between month 2 & 3
And so on....
Then sum all the above average and divide by 12.
- Any help is much appreciated 👏
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your prompt reply! @bhanu_gautam
Hi @TsW08
Based on your needs, I have created the following table.
Then you can try the following dax to get "total cost", "average between two months", "average divide by 12".
Total =
VAR _month = SELECTEDVALUE('Table'[Month])
VAR _year = SELECTEDVALUE('Table'[Year])
RETURN
CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[Year]=_year && 'Table'[Month]=_month))
Average =
VAR _month = SELECTEDVALUE('Table'[Month])
VAR _year = SELECTEDVALUE('Table'[Year])
VAR _current = CALCULATE([Total],FILTER(ALL('Table'),'Table'[Year] = _year && 'Table'[Month] = _month))
VAR _previous = CALCULATE([Total],FILTER(ALL('Table'),'Table'[Year] = _year && 'Table'[Month] = _month - 1))
RETURN IF(_previous = BLANK(),[Total],(_current + _previous)/2)
average and divide by 12 =
var _year = SELECTEDVALUE('Table'[Year])
RETURN
SUMX(FILTER(ALL('Table'),'Table'[Year]=_year),'Table'[Average])/12
Result:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your prompt reply! @bhanu_gautam
Hi @TsW08
Based on your needs, I have created the following table.
Then you can try the following dax to get "total cost", "average between two months", "average divide by 12".
Total =
VAR _month = SELECTEDVALUE('Table'[Month])
VAR _year = SELECTEDVALUE('Table'[Year])
RETURN
CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[Year]=_year && 'Table'[Month]=_month))
Average =
VAR _month = SELECTEDVALUE('Table'[Month])
VAR _year = SELECTEDVALUE('Table'[Year])
VAR _current = CALCULATE([Total],FILTER(ALL('Table'),'Table'[Year] = _year && 'Table'[Month] = _month))
VAR _previous = CALCULATE([Total],FILTER(ALL('Table'),'Table'[Year] = _year && 'Table'[Month] = _month - 1))
RETURN IF(_previous = BLANK(),[Total],(_current + _previous)/2)
average and divide by 12 =
var _year = SELECTEDVALUE('Table'[Year])
RETURN
SUMX(FILTER(ALL('Table'),'Table'[Year]=_year),'Table'[Average])/12
Result:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@TsW08 , Create a calculated column for monthly totals
MonthlyTotal =
CALCULATE(
SUM(StaffCostings[Cost]),
ALLEXCEPT(StaffCostings, StaffCostings[Year], StaffCostings[Month])
)
Create a calculated column for the moving average between consecutive months:
DAX
MovingAverage =
VAR CurrentMonth = StaffCostings[Month]
VAR CurrentYear = StaffCostings[Year]
VAR PreviousMonth = IF(CurrentMonth = 1, 12, CurrentMonth - 1)
VAR PreviousYear = IF(CurrentMonth = 1, CurrentYear - 1, CurrentYear)
VAR PreviousMonthTotal =
CALCULATE(
SUM(StaffCostings[MonthlyTotal]),
StaffCostings[Year] = PreviousYear,
StaffCostings[Month] = PreviousMonth
)
RETURN
IF(
ISBLANK(PreviousMonthTotal),
BLANK(),
(StaffCostings[MonthlyTotal] + PreviousMonthTotal) / 2
)
Create a measure to sum the moving averages and divide by 12
DAX
FinalAverage =
VAR MovingAverages =
CALCULATETABLE(
VALUES(StaffCostings[MovingAverage]),
NOT(ISBLANK(StaffCostings[MovingAverage]))
)
VAR SumOfMovingAverages = SUMX(MovingAverages, [MovingAverage])
RETURN
SumOfMovingAverages / 12
Proud to be a Super User! |
|
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for the fast response. The formula seems to return an empty column (see images below). Does this look correct?

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
12-20-2024 06:44 AM | |||
01-02-2025 07:26 AM | |||
02-04-2025 05:05 PM | |||
08-15-2024 08:50 PM | |||
07-30-2024 05:40 PM |
User | Count |
---|---|
22 | |
12 | |
10 | |
10 | |
8 |
User | Count |
---|---|
16 | |
15 | |
14 | |
13 | |
10 |