I am trying to create a dashboard that shows a variety of different calculation for districts in California.
I have a dataset with data for each school in each district which is disaggregated by Gender and by Race/Ethnicity (i.e. 1 row for african american males for school 1, another row for african american females for school 1, etc.). I want data aggregated by district.
I would like to show the following things in my dashboard:
- Total A-G Completion Rate for the district
- The difference between the African American and White A-G Completion Rate for the district
- The difference between the Hispanic and Latino and White A-G Completion Rate for the district
All of this will be shown by District, which means the data for each school is aggregated per district.
The A-G Completion is calculated correctly, however, I am struggling to figure out how to calculate the difference between the A-G Rates by Race/Ethnicity.
Essentially what I want to do with my calculation is (A-G Rate for Whites for District 1) – (A-G Rate for African Americans for District 2). I have tried the following calculations listed below, but all end up showing me Null values. Any ideas?
Here is a link to one of the resources I used: http://kb.tableau.com/articles/knowledgebase/calculation-error-resolving-aggregate-nonaggregate-arguments
Also, could this be a case where I use LOD Expressions? I tried playing around with them, but couldn't figure them out.
African American A-G Rate:
WHEN "African American, Not Hispanic" THEN [A-G Rate] END
African American Rate 2:
IF ATTR([Definition])="African American, Not Hispanic" THEN sum([Uc Grads]/[Grads]) END
African American Rate 3:
(IF ATTR([Definition])="African American, Not Hispanic" THEN SUM([Uc Grads]) END/
IF ATTR([Definition])="African American, Not Hispanic" THEN SUM([Grads]) END)
I'm attaching my dataset and my .tbx file
The data has 3 tabs which I've joined: "data" is the raw data, "Sheet2" has definitions for the race/ethnicity coding in data, and "College Futures Regions" has regional values based on counties.