Forum Discussion
Helper IIAverage Revenue per Month
Hi,
I have these tables:
| Date |
| 1/1/21 |
| 1/2/21 |
| 1/3/21 |
| 1/4/21 |
| 1/5/21 |
| 1/6/21 |
| 1/7/21 |
| 1/8/21 |
| 1/9/21 |
| 1/10/21 |
| 1/11/21 |
| 1/12/21 |
| Start of Month | CustomerID | Revenue |
| 1/1/21 | 1 | 10 |
| 1/1/21 | 2 | 20 |
| 1/1/21 | 3 | 30 |
| 1/1/21 | 4 | 40 |
| 1/2/21 | 1 | 50 |
| 1/2/21 | 2 | 60 |
| 1/2/21 | 3 | 70 |
| 1/3/21 | 1 | 80 |
| 1/3/21 | 2 | 90 |
| 1/3/21 | 3 | 100 |
| 1/4/21 | 1 | 110 |
| 1/4/21 | 2 | 120 |
I'm looking for dax that show me the average revenue per month/period for example:
01/21 -> (10 + 20 + 30 + 40) / 4 = 25
02/21 -> (50 + 60 + 70) / 3 = 60
03/21 -> (80 + 90 + 100) / 3 = 90
04/21 -> (110 + 120) / 2 = 115
01/21 + 02/21 = (25 + 60) / 2 = 42.5
01/21 + 02/21 + 03/21 = (25 + 60 + 90) / 3 = 58.333
Thanks all!
Hi dolevh
You may try these two Measures.
AvgRevenuePerMon =
CALCULATE (
AVERAGE ( 'DataTable'[Revenue] ),
ALLEXCEPT ( 'DataTable', 'DataTable'[Start of Month] )
)
AvgRevenuePrePeriod =
VAR _seldate =
SELECTEDVALUE ( 'DataTable'[Start of Month] )
VAR min_start_mon =
CALCULATE ( MIN ( 'DataTable'[Start of Month] ), ALL ( 'DataTable' ) )
VAR _culrevenue =
SUMX (
FILTER (
ALLSELECTED ( 'DataTable'[Start of Month] ),
'DataTable'[Start of Month] <= _seldate
),
[AvgRevenuePerMon]
)
VAR _count =
IF (
_seldate = min_start_mon,
0,
CALCULATE (
DISTINCTCOUNT ( 'DataTable'[Start of Month] ),
FILTER ( ALLSELECTED ( 'DataTable' ), 'DataTable'[Start of Month] <= _seldate )
)
)
RETURN
DIVIDE ( _culrevenue, _count )
Then, the result should look like this:
Attached pbix file as a reference.
Best Regards,
Community Support Team _ Caiyun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it, please feel free to let us know. Thanks a lot!
3 Replies
- Greg_Deckler
Community Champion
dolevh Try:
Measure = VAR __Table = SUMMARIZE('Table2',[Start of Month],"__Average",AVERAGE('Table2'[Revenue])) RETURN AVERAGEX(__Table,[__Average])Basically, the measure aggregation pattern. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
dolevhit's not true unfortunately 😞
v-cazheng-msftCommunity Support
Hi dolevh
You may try these two Measures.
AvgRevenuePerMon =
CALCULATE (
AVERAGE ( 'DataTable'[Revenue] ),
ALLEXCEPT ( 'DataTable', 'DataTable'[Start of Month] )
)
AvgRevenuePrePeriod =
VAR _seldate =
SELECTEDVALUE ( 'DataTable'[Start of Month] )
VAR min_start_mon =
CALCULATE ( MIN ( 'DataTable'[Start of Month] ), ALL ( 'DataTable' ) )
VAR _culrevenue =
SUMX (
FILTER (
ALLSELECTED ( 'DataTable'[Start of Month] ),
'DataTable'[Start of Month] <= _seldate
),
[AvgRevenuePerMon]
)
VAR _count =
IF (
_seldate = min_start_mon,
0,
CALCULATE (
DISTINCTCOUNT ( 'DataTable'[Start of Month] ),
FILTER ( ALLSELECTED ( 'DataTable' ), 'DataTable'[Start of Month] <= _seldate )
)
)
RETURN
DIVIDE ( _culrevenue, _count )
Then, the result should look like this:
Attached pbix file as a reference.
Best Regards,
Community Support Team _ Caiyun
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it, please feel free to let us know. Thanks a lot!
