4 Replies Latest reply on Jul 26, 2016 2:25 PM by Michael Peterson

Create rates by dividing dimensions by a specific measure value

Hello! This issue seems like it has to have been asked before, and perhaps how I'm describing the issue is the problem, but I've not found a solution while scouring old questions/answers.

My data are counts of firms in my state by different demographic characteristics for the owners. My data are structured as:

 Veteran Status Gender Race Hispanic Origin Year Number of firms with or without paid employees All All All All 1997 410,634 All Female-owned All All 1997 108,417 All Male-owned All All 1997 216,926 All Equally male-/female-owned All All 1997 76,675

I'd like to calculate the share of all business owners each group has. In the case of female-owned businesses in 1997 you'd calculate 108,417 / 410,634 to accomplish that but my issue is that the denominator is different each year (businesses close, new businesses open), so that what I really need is a calculated field(s) that would apply that division to all of my groups based on the firm total for a given year, so that its grabbing the right numerator and denominator from each row:

1997 women / 1997 total

1998 women / 1998 total

1999 veterans / 1999 veteran total

Etc

Any ideas? Thanks in advance for your wisdom!

• 1. Re: Create rates by dividing dimensions by a specific measure value

Hey Michael,

It sounds like you need a Level of Detail calculation.

Something like this: SUM([Owners]) / MAX({FIXED [Year] : SUM([Owners])})

Edit: With your data, I think you want this:

SUM([Number of firms with or without paid employees]) / MAX({FIXED [Year] : SUM([Number of firms with or without paid employees])})

Or maybe this, if you want to only grab that All/All/All row:

SUM([Number of firms with or without paid employees]) / MAX({FIXED [Year] : SUM(IF [Gender] = 'All' AND [Race] = 'All' AND [Hispanic Origin] = 'All' THEN [Number of firms with or without paid employees] END)})

1 of 1 people found this helpful
• 2. Re: Create rates by dividing dimensions by a specific measure value

Thanks - really appreciate your quick response!

So just eyeballing that second formula you suggested, it seems to be getting me close, but i'm not getting the correct percentages when I apply it in Tableau (i calculated all of these in Excel for the client, so I've got the correct percentages on hand). Curiously when I do a quick bar chart of my race groups using this new field, the distribution appears to be similar (or potentially the same) but the percentages are all lower than each racial group's actual share.

I recognize this is probably vague so let me know if I can provide you with additional info to help diagnose.

• 3. Re: Create rates by dividing dimensions by a specific measure value

Hey Michael,

The way this calculation is setup, you would need to be pretty specific about how you break out the data. Looking at your data, it is pretty cluttered with the duplicate "All" records, so you will need to setup your viz to either use only those or exclude them.

With some careful filters, you should end up with something that ties out:

1 of 1 people found this helpful
• 4. Re: Create rates by dividing dimensions by a specific measure value

Ok it seems like you've got me on a good path here. Thanks again.