8 Replies Latest reply on May 12, 2018 6:32 AM by Zhouyi Zhang

Attempting to calculate difference in measure dependent on dimension

Hi all,

I'm looking for help in working out how to create a calculation based on a few items in my data. This is sample data, it's more the process of how to do it that I would need to move forward with my project. I'm trying to do this in Tableau Desktop 2018.1

The data I have is as follows:

 Item Area Unit Cycle Entity 1 Los Angeles 1000 1 Entity   1 Los Angeles 800 2 Entity 2 Los Angeles 400 1 Entity   2 Los Angeles 450 2 Entity 3 Los Angeles 500 1 Entity   3 Los Angeles 1500 2 Entity 4 Los Angeles 180 1 Entity   4 Los Angeles 170 2 Entity 5 Los Angeles 1000 1 Entity   5 Los Angeles 500 2

I am looking to calculate the change in unit number between cycle 2 and cycle 1, so it looks like the following:

 Item Area Unit Change Entity 1 Los Angeles 800 -200 Entity 2 Los Angeles 450 50 Entity 3 Los Angeles 1500 1000 Entity 4 Los Angeles 170 -10 Entity 5 Los Angeles 500 -500

Also, attached a packaged workbook with the above data.

Any help would be appreciated, thanks.

• 1. Re: Attempting to calculate difference in measure dependent on dimension

David,

The simplest/easiest approach would look something like this:

SUM(IF [Cycle]=2 THEN [Unit] END

/

SUM(IF [Cycle]=1 THEN [Unit] END

This would require that you have [Item] in your sheet to view the correct results by entity instead of in aggregate.

Just let me know if you were looking for something else.

-Wesley

If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 2. Re: Attempting to calculate difference in measure dependent on dimension

I forgot to include some additional information in the original questions, my apologies.

I only want to see one cycle at a time in the actual end result, so whilst I am comparing the values of Cycle 1 against Cycle 2 I only want to see the Cycle 2 items in the data, so in my example above, the 5 entities and units all relate to cycle 2. At the moment I've been filtering the cycle to be 2.

I've not played around with parameters at all so not sure if possible, but as I will be having more cycles to add in the future is there a way to have the calculated field based on parameters of the cycle I set e.g. cycle 4 against cycle 2?

• 3. Re: Attempting to calculate difference in measure dependent on dimension

Hi, David

Is it something like this? if yes, please find attached workbook.

Hope this helps

ZZ

• 4. Re: Attempting to calculate difference in measure dependent on dimension

Hi Zhouyi,

That's works perfectly thanks very much!!!

I would have to edit the calculated field every time I want to change the cycle comparion e.g. the change from a future 4 to 2. Is there a way I can get it to work with parameters or filtering where I select the moderation cycles to compare without having to edit the calculated field?

• 5. Re: Attempting to calculate difference in measure dependent on dimension

One more thing, I seem to be running into another problem that it's not showing the correct calculation for multiple territories.

These all refer to one entity:

When I add the territory to the fixed range in the calculated field I can narrow it down to this:

But the data is still incorrect. Any thoughts?

• 6. Re: Attempting to calculate difference in measure dependent on dimension

Hi, David

To your 1st question, you can create two parameters to avoiding hard code.

To you 2nd question, do you have any other filters? or can you share you sample workbook with issue?

ZZ

• 7. Re: Attempting to calculate difference in measure dependent on dimension

Thanks very much again, I figured out the issue with my second question - I had to change it from SUM(IF to MIN(IF which then returned the correct calculations.

Also thanks for sending the parametrpa info .looks like I was already on the right lines.

Much appreciated .

• 8. Re: Attempting to calculate difference in measure dependent on dimension

No worries.

ZZ