Complicated Dynamic Calculation  Need help from the Community
Tuyen Nguyen Jul 29, 2018 6:01 AMHi,
I'm struggling with a complicated calculation and need help from the community.
To describe my problem, I'll use the following sample dataset in which the measure is headcount.
Year  Nationality  Division  Type  Level  Headcount 
2010  US  IT  PT  A  99 
2010  UK  Finance  FT  A  58 
2010  AU  HR  PT  C  41 
2010  JP  HR  PT  B  15 
2010  CN  IT  FT  C  5 
2010  MX  IT  PT  A  20 
2010  CN  Finance  PT  B  32 
2010  US  HR  PT  C  53 
2010  AU  IT  FT  B  22 
2011  CN  IT  FT  A  90 
2011  MX  Finance  FT  B  80 
2011  CN  HR  FT  B  91 
2011  US  HR  PT  C  41 
2011  AU  IT  FT  A  95 
2011  US  Finance  FT  C  21 
2011  UK  HR  PT  B  87 
2011  AU  HR  FT  B  78 
2011  JP  IT  FT  C  92 
What I need is a calculated measure called Diversification Ratio (DRatio)
The important thing is that Si must always be calculated by Nationality at any level of granularity, and M is the grand total of Headcount and must be dynamic so that it changes base on the level of granularity.
The problem is how to make the calculation of the DRatio dynamic so that whenever I apply filters or change the level of granularity, DRatio must change accordingly.
For example, I want to have a line graph showing how DRatio changes over time, so the DRatio need to be calculated by year
e.g. DRatio for 2010 = 0.7275
Year  Nationality  S  M  P  P*(1P)  DRatio 
2010  US  152  345  0.44058  0.2464692  0.727528 
2010  UK  58  345  0.168116  0.139853  0.727528 
2010  AU  63  345  0.182609  0.1492628  0.727528 
2010  JP  15  345  0.043478  0.0415879  0.727528 
2010  CN  37  345  0.107246  0.0957446  0.727528 
2010  MX  20  345  0.057971  0.0546104  0.727528 
Now, if I need to drill it down to Division level, let say a graph showing DRatio change over time for HR, so now, the DRatio must be calculated by Year and Division
e.g. DRatio for HR in 2010 = 0.6031
Year  Division  Nationality  S  M  P  P*(1P)  DRatio 
2010  HR  AU  41  109  0.3761468  0.23466  0.603148 
2010  HR  JP  15  109  0.1376147  0.118677  0.603148 
2010  HR  US  53  109  0.4862385  0.249811  0.603148 
Again, If I drill down more, let say DRatio over time by Division by Level, then again DRatio must be recalculated at "YearDivisionLevel" level
I don't know how to do it in Tableau at the moment, so really appreciate any help or idea from you guys!
Cheers,

Diversification.twbx 5.2 KB