topic Re: monthly average customer revenue in DAX Commands and Tips

monthly average customer revenue

Anonymous — Tue, 17 Nov 2020 23:15:54 GMT

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

Re: monthly average customer revenue

Anonymous — Tue, 17 Nov 2020 23:24:32 GMT

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

Re: monthly average customer revenue

camargos88 — Tue, 17 Nov 2020 23:25:18 GMT

Anonymous ,

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-field-is-aggregated

Re: monthly average customer revenue

amitchandak — Wed, 18 Nov 2020 04:35:22 GMT

Anonymous , 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])

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

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

Re: monthly average customer revenue

Anonymous — Wed, 18 Nov 2020 14:07:58 GMT

Thank you!!

Re: monthly average customer revenue

Anonymous — Wed, 18 Nov 2020 14:59:57 GMT

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

Re: monthly average customer revenue

Anonymous — Thu, 19 Nov 2020 10:00:45 GMT

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!

Re: monthly average customer revenue

Anonymous — Thu, 19 Nov 2020 10:22:53 GMT

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'