Average number

user07693

Hi,

 

Please help. It should be easy but I can't find the solution.

 

I have the following data:

 

Date; Bank; Amount

10/29/17; Citi; $100

10/29/17; Citi; $200

10/29/17; Citi; $300

10/30/16; Citi; $1000

 

I need to calculate average daily amount for Citi Bank for the period 10/29/17-10/30/17.

The calculation should be as follows;

1) first total per day needs to be calculated 10/29/17: 100+200+300=600 ; 10/30/17: 1000

2) now average: (600 + 1000) / 2 = 800

 

I've tried just change the aggregetion to average but this resulted into the following calculation: (100+200+300+1000)/4 = 400, which is not what I'm looking for

BlueRooster

What card type are you trying to do this for? If it's a table card, then one thing you can do is to set the Amount field to AVG(Amount), that should break it down by Day/Bank/Amount for each instance of day and bank.

 

You can then add a Subtotal or Total Row to see the total average.

 

If you can provide some more info around how you're trying to display your data, I may be able to give you some additional pointers on how to accomplish what you're wanting.

 

Sincerely,

Colt

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

user07693

it is for single bar. I have other banks besides Citi, so taking my example I need to see on the X axis Bank and Y for showing averages

BlueRooster

OK, in that case what you'll need to do is to create a data transform on your data before trying to show it in the card.

 

You'll want to connect to dataset  you're using, Group By the date field while selecting the amount field as your aggregate field. This way it will sum up the values on for each date ahead of time.

 

Once you have created a new dataset with this, you can then create the card you're looking for and get the correct average numbers.

 

Please let me know if you need a more detailed walk through.

 

Sincerely,

Colt

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

user07693

I'm very new to data transform more detailed workflow would be very cool