cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
edayan
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
amitchandak
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
edayan
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'

edayan
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!

 

edayan
Frequent Visitor

Thank you!!

edayan
Frequent Visitor

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

 

Capture1.PNGCapture2.PNGCapture3.PNG

amitchandak
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

camargos88
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!



edayan
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

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.

Top Solution Authors