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

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

7 REPLIES 7
Frequent Visitor

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

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!

Super User III

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

#### Welcome to the User Group Public Preview

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

#### Check it Out!

Click here to read more about the March 2021 Updates!

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

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors