cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

monthly average customer revenue

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

1 ACCEPTED SOLUTION
Super User IV
Super User IV

@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")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

7 REPLIES 7
Frequent Visitor

Capture.PNG

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'

Frequent Visitor

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!

 

Frequent Visitor

Thank you!!

Frequent Visitor

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

 

Capture1.PNGCapture2.PNGCapture3.PNG

Super User IV
Super User IV

@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")



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Super User III
Super User III

@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...



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Frequent Visitor

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.