5 Replies Latest reply on Mar 29, 2012 4:35 PM by Dimitri.B

# Show sum of one measure for all dates but average of another measure on other dates

What I'm specifically trying to do is show on a map the sum of a KPI over the past x days (for example sum of sales), however I want to GPS coordinates to be the ones of the last date, not the average of all dates.

How could I achieve this?

• ###### 1. Re: Show sum of one measure for all dates but average of another measure on other dates

One way to do it is via table calculations, as in the attached (I have modified the data a bit to demonstrate it better).

Use day filter to remove day 4, 3, etc. and observe the dot on the map move to reflect the last lat and lon, or do the same in the 'data' sheet, where the change is even more obvious.

Table calculations are not the easiest concept in Tableau, and in this case both formula and partitioning are fairly complicated (shown below, important bits highlighted).

Also, because it is a table calc - making changes to the sheet will at least require re-partitioning of the calculation to suit new layout or can even render it useless. I assume the workbook you supplied is just a sample data, so you'll have to try to adapt this solution to your real situation.

• ###### 2. Re: Show sum of one measure for all dates but average of another measure on other dates

Thanks Dimitri,

The issue I have with this is that when I filter on the last date, I get the "KPI" measure only on that date.

The real workbook, which unfortunately I can't post, is connected to a database and is refreshed daily. The DB has years of data but I want to show the sum of sales on the 30 days preceding a date (I use a filter and a parameter for that as the dates needs to be set across several data connections).

If I used the technique above, it would only show 1 day of sales or have I missed something?

• ###### 3. Re: Show sum of one measure for all dates but average of another measure on other dates

Here is a better example. The only thing I want to change is the average coordinates, but I want to keep the sum of sales on 30 days.

• ###### 4. Re: Show sum of one measure for all dates but average of another measure on other dates

The filter trick is only to demonstrate that the solution works, you don't need to do any filtering on your real data to make this work.

If you don't apply any filters at all, then the last lat/lon will still show values from the last day only, not average of all, while your KPI will be the aggregate of all data.

• ###### 5. Re: Show sum of one measure for all dates but average of another measure on other dates

Same approach will work in this workbook, don't worry about the filters.