6 Replies Latest reply on Aug 22, 2013 2:13 PM by Shawn Wallwork

# Get sum of measure based on distinct dimensions

I have an excel database that I've reshaped using the Tableau excel add-in.

I'm trying to build a map that show the "days in field" (DiF) value by province, the problem is that the DiF value is obviously repeated many times due to the reshaping of data.

Is there a way to do something like Sum DiF, but only for Unique "ProjectID"? Right now my numbers are all very inflated.

• ###### 1. Re: Get sum of measure based on distinct dimensions

Yes Mike there is a way. But you'll have to read this, especially #1 & #3, so we can better help you. Thanks,

--Shawn

• ###### 2. Re: Get sum of measure based on distinct dimensions

As Shawn said, yes is the answer.  How you do it depends on a number of factors.  If I am understanding you, you are wanting to display the sum([DiF]) for different values of a dimension.

You can place ProjectID on the filter shelf, and select the project you want to display that way, then the sum will only apply to that distinct dimension.  You can also write calculations, like:

IF[ProjectID]="Project1" then sum(DiF) end

There are many options, depending on what you want, which is why I believe Shawn directed you to the link.  To best address your issue, we'd need a sample packaged workbook (preferred) or screenshots in order to better understand the setup of your data.  Also, a description of what end result you are looking for in your visualization will help.

1 of 1 people found this helpful
• ###### 3. Re: Get sum of measure based on distinct dimensions

Thanks Matthew,

I've got a field called DiF and I want to get a the total of the value across all my unique project ID's. What you suggested would work if I was just wanting the value for a single project, but as I'm looking for an overall number, it won't do what I want.

When I look at the underlying data, I can see that a project is repeated 10-15 times, as I've reshaped the data in a way that DiF is a measure.  For most of the data in the file, I have a "data" field, and a "metric" field, and I'm typically filtering on the metrics. Perhaps I should have made DiF work the same way, and that might be what I have to do, but I was just wondering if there was an easy way to sum a measure, but have it only appear once without going back to my original data.  For now I've actually just filtered it on a random metric that happens to exist for each record, so I'm getting the right value, just not sure if that is the best way to go about it.

I'll see if I can package something up, but it's a bit of a beast and it is all private data so trying to blind it or recreating it in a new file with dummy data will be a lot of work.  Anyways, if this helps, great, if not, I'll have to see what I can figure out.

Thanks for the feedback though.

Cheers

• ###### 4. Re: Get sum of measure based on distinct dimensions

See Matthew, the quick easy answers always (well almost always, unless you're Alex) lead to a long back and forth that eventually ends in a "please post a packaged workbook". I'm just sayin'....

--Shawn

• ###### 5. Re: Get sum of measure based on distinct dimensions

Yes, but as a fairly new Tableau user working with sensitive data, I know it is not always easy to do this when you have little experience. For me, just pointing to the link can be frustrating and confusing to a newcomer.

• ###### 6. Re: Get sum of measure based on distinct dimensions

Duly noted. In the future I'll mention using SuperStore to recreate a similar problem, in my responses. You've made me realize that doc was created for the benefit of us forum junkies, and not the new user (although many seem to find it useful). Thanks for pointing that out Matthew.

Cheers,

--Shawn