12 Replies Latest reply on Aug 16, 2018 1:00 PM by Joe Oppelt

# Calculation help

Hello,

looking for help to figure out a problem i am having in Tableau.  I am trying to create a table showing a breakdown of total group area by group name.  In this example, i would like to show how much square footage is allocated to Dept 1 and Dept 2.  Because the data set contains multiple duplicate rows of data, i am not able to get an accurate area calculation for each group.  Room 638 should total 117.72 and room 642 should total 130.87.  Is there a way to create a calculation that omits the duplicate rows?  This is a small data set in excel as an example.

Thanks.

• ###### 1. Re: Calculation help

Use a FIXED LOD to get the MAX among all rows with identical dimensional values.

At a quick glance I think it will look like this:

{ FIXED [Building Description],[floor], [function description],[group],[department] (, and any other dimensions that comprise an identical row) : MAX([Sq. ft]) }

SUM([that calc]) should get you what you need.

If you need further help with this, I'm going to need a sample workbook with some sample data.

• ###### 2. Re: Calculation help

You could create an LOD calculation like this, which should address the issue with duplicate rows.

{FIXED [Room] : MAX([Room Sqr Feet])}

Then SUM that value.

See attached workbook and sample data set.

• ###### 3. Re: Calculation help

Thanks, I tried the calculation provided but it is still calculating the duplicate rows.  Attached are the sample data and workbook.

The end product should be that Dept 1 = 159.62 and Dept 2 = 88.98.

Thanks.

1 of 1 people found this helpful
• ###### 4. Re: Calculation help

Thanks for trying but it did not work.

• ###### 5. Re: Calculation help

Still not working? Any chance you could share a small sample of your data (or anonymize it) so we can take a closer look?

• ###### 6. Re: Calculation help

My suggestion was to grab the MAX in the FIXED LOD, not the SUM.  MAX (or MIN) brings back one value from all the rows.  SUM  sums up all the values in all the rows.

Change it to MAX and see what that does for you.

• ###### 7. Re: Calculation help

I just tried my calc with your data and I'm getting 248.59 for each Group Name. Is that not what you are expecting?

• ###### 8. Re: Calculation help

I tried MAX but it eliminates the smaller area assigned to Dept 2. In room 638, Dept 1 has 80% of the space allocated to it or 94.18 SF, and Dept 2 has 20% or 23.54 SF while room 642 is split in half between the two departments with 65.44 SF allocated to each.  I am trying to get the total area allocated to Dept 1 and Dept 2 that is assigned to each room. I'm now realizing that this probably can't be achieved without fixing the data set to remove the multiple unnecessary rows.

Thanks.

• ###### 9. Re: Calculation help

OK, I just saw your note above. Try the following formula:

{FIXED [Group Name],[Room] : MAX([Group Area])}

See attached workbook.

• ###### 10. Re: Calculation help

Changing it to MAX gives you the two numbers you are looking for.

Putting ROOM on your sheet shows different values at the ROOM level.

See attached,

• ###### 11. Re: Calculation help

Note that I made the change in the calc, not on the text shelf.

• ###### 12. Re: Calculation help

That worked, Thank you very much!