Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
@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])
or
averageX(summarize(Table, Table[ID], "_1", sum(Table[revenue])),[_1])
Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")
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'
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!
Thank you!!
@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])
or
averageX(summarize(Table, Table[ID], "_1", sum(Table[revenue])),[_1])
Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")
@Anonymous ,
Drag the date and revenue values to a table visual, change the revenue aggregation to average.
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
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
40 | |
20 | |
12 |