6 Replies Latest reply on May 3, 2018 6:01 PM by Shinichiro Murakami

# Totals over year calculated measure

Hello,

I have the following case where I need to

1 Display expenses

2 Display Las Year expenses side by side

3 Display totals for both columns

Problem 1

Some last year expenses can be blank cells, so I generated a calculated measure instead

Last Year =ZN(lookup(lookup(SUM([Gasto]),-1),0))

This calc measure puts a zero if there is a blank and also gets the last year amount (but,,, the Total Column displays only zeros...)

Problem 2

I defined the year filter as follows:

STR(lookup(attr([Ejercicio]),0))

This is so I can filter Ejercicio (year) and still be able to see "Last Year" data side by side to the selected year... but... I also see the Total column adding the "hidden" years...

So, I need to see each Years corresponding last year column,,, while only adding visible columns in the Totals to the right.

Any ideas on how to manage these two cases simultaneously (in the same table).

Thank you,

• ###### 1. Re: Totals over year calculated measure

HI Cecilia

Could you attach packaged workbook (**.twbx).

The solution anyways needs to duplicate data source, and need to understand your data structure.

Thanks,.

Shin

• ###### 2. Re: Totals over year calculated measure

Here it is...

• ###### 3. Re: Totals over year calculated measure

I attached the file, but now I cant see it, and cant find a way to attach it again?

• ###### 4. Re: Totals over year calculated measure

Because you are on the inbox view.

I can see it and will look into in soon.

Shin

• ###### 5. Re: Totals over year calculated measure

Sorry yours are connected to tableau online and I cannot to see it.

Could you please extract data source with filter to reduce the data size, then create packaged workbook.

Thanks,

Shin

• ###### 6. Re: Totals over year calculated measure

Can you try with this one?

Thank you

• ###### 7. Re: Totals over year calculated measure

Hi Cecilia,

This type of customized table view is one of the biggest headache on Tableau.

Here is one idea, even though I am not sure your connection allows union or not.

If not, us Tableau Prep to shape the data source.

Plus in this case, add parameter is way easier than using lookup filter.

I tried to replicate your data, but it was too much troublesome, so attached simple sample.

Union creates table name.

Thanks,

Shin