6 Replies Latest reply on Oct 24, 2018 2:23 AM by Sujeet kumar

# To get data for particular Month

I want to find the average of a particular month

Example:  I have applied this formula :   AVG({INCLUDE [Month]= 'July': SUM([Collection])/ SUM([No.Of Vehicles])}) and write this formula on ROW in tableau

I have Month column(June, July, August). I want to find per vehicle average of month July. So I would divide the sum of the collection in July to no. of vehicles in July.

What is the wrong with the above formula?

• ###### 1. Re: To get data for particular Month

Hi Sujeet,

It would be helpful if you attache the sample workbook and/or sample data for this.

I'm afraid Collection and No.Of Vehicles should be dimensions which cannot be aggregate by SUM, you should use COUNTD instead to count measures.

And I'm not sure what sum(collection)/sum(No. Of Vehicles) means either, so I cannot say that calculation is collect.

Best regards,

Hideki OGAWA

• ###### 2. Re: To get data for particular Month

This is the sample data. What I want to calculate collection per vehicle for a particular month.

For example June: total collection: (4500+234+314253+32435+57+89876+ 1234+213+12425) = 455227

Total Vehicle June= 9

Collection/Vehicle for June= 50580

So what should I write on the row of tableau to get this? • ###### 3. Re: To get data for particular Month

Then both of them are measure.

I believe you can do that without LOD, SUM(A)/SUM(B) and fliter by month to July, it's simple.

Or you can use LOD {FIXED [Month]: SUM(A)/SUM(B)} and filter by month to July, but I don't understand why you need this.

I have attached the sample workbook using data aggregated from Superstore data.

Is that what you want?

Please let us know you want something different.

Best regards,

Hideki OGAWA

• ###### 4. Re: To get data for particular Month

I want to compare the revenue/vehicle of month August to an average of revenue/vehicle of June and July together.  I want two line in a graph one will show revenue/vehicle of August and other will show average revenue/ vehicle of June and July together.

This could then be averaged across another dimension (eg. venue then car type then model) when dragged into the view.

• ###### 5. Re: To get data for particular Month

Then it should be like this, to allow any dimension to jump in;

AVG({INCLUDE [Month]:SUM([Sales])/SUM([Quantity])})

This is based on the assumption you need June and July together but want to calculate average of revenue/vehicle of both months.

If you just want to combine June and July, just SUM(A)/SUM(B) should be enough.  Please see the difference between the two types of calculation.

Best regards,

Hideki OGAWA

• ###### 6. Re: To get data for particular Month

Thanks for the reply. If I want to find the average for July month only then I should write :

AVG({INCLUDE [Month]= 'July' :SUM([Sales])/SUM([Quantity])})  or something else.