monthly average customer revenue

[edayan]

Hi Everyone

My dataset looks as follows:

No          Revenue              Date

1              10000                    Jan-20

2              121211                  Jan-20

3              121212                  Jan-20

.

.

1              111112                  Feb-20

2              111123                  Feb-20

3              111134                  Feb-20

4              12134                    Feb-20

5              234234                  Feb-20

I’d like to create a measure which can calculate the monthly average customer revenue. Can someone help? Thanks

[edayan]

I thought this would solve the issue but i get an error that 

'a table of multiple values was supplied where a single value was expected'

[edayan]

is there a way to include this onto a measure and not a table? would be more elegant if these averages aligned on top of each other. Many thanks!

[edayan]

Thank you!!

[edayan]

Is there any way to have it impact both tables? @amitchandak thanks

[amitchandak · Accepted solution]

@edayan , Try a measure like one of the two . Create a column Month year  either in Table or date table

averageX(summarize(Table, Table[ID], table[Month  year], "_1", sum(Table[revenue])),[_1])

or

averageX(summarize(Table, Table[ID], "_1", sum(Table[revenue])),[_1])

Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")

[camargos88]

@edayan ,

Drag the date and revenue values to a table visual, change the revenue aggregation to average.

https://docs.microsoft.com/en-us/power-bi/create-reports/service-aggregates#change-how-a-numeric-fie...

[edayan]

Just pointing out that No represents No. of invoice so not a unique customer #. It is representing the number of customers i have in that month so for example January - Average of Jan-20 3 revenue lines. Many thanks