Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to plot the average transaction count and total value per calendar month (e.g., we average 20 transactions, and $100M, in September each year) in a bar chart from a table that has the date and value of each transaction.
Say I have the following table that has the date and value of each transaction (only showing transactions from last and current September for simplicity):
Date, Value
9/1/16, 100.00
9/2/16, 200.00
9/1/17, 300.00
9/2/17, 400.00
With this data, we average 2 transactions, and $250.00, each September.
I created a master dimension on calendar month ([Txn Month]), which is just Jan, Feb, Mar, etc.
I added a bar chart.
I set the dimension to [Txn Month]. I don't know how to express either measure. Based on what I found on this forum, I tried using
Avg(Aggr(Sum([Value]), [Txn Month]))
Avg(Aggr(Count([Value]), [Txn Month]))
but nothing shows up in the bar chart other than the [Txn Month] axis dimension values. (No bars show up for either measure.) I tried adding just the Aggr() expressions to tables to see what was in there, but nothing displayed.
Any ideas?
Thanks
Do you see the total value for the month when just using
=Sum(Value)
? If not, you need to check your data model and data (e.g. Value needs to be interpreted as number when reading in).
For your averages, try
=Sum(Value) / Count(Value )
=Count(Value) / Count(DISTINCT Monthstart(Date) )
Instead of Monthstart(Date) (which also only works when Date is interpreted as date showing a dual value with a numeric part), you can use any field that represents a unique Month & Year combination.
Hello Buddy , i modified your code to
=Sum(Value) / Count(DISTINCT Monthstart(Date) )
for calculating the average , and it worked perfectly.
would you please give me an idea to do the calculation of the min and max basd on the date?