3 Replies Latest reply on Jul 30, 2018 2:46 AM by Naveen B

# Complicated Dynamic Calculation - Need help from the Community

Hi,

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 (D-Ratio)

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 D-Ratio dynamic so that whenever I apply filters or change the level of granularity, D-Ratio must change accordingly.

For example, I want to have a line graph showing how D-Ratio changes over time, so the D-Ratio need to be calculated by year

e.g. D-Ratio for 2010 = 0.7275

 Year Nationality S M P P*(1-P) D-Ratio 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 D-Ratio change over time for HR, so now, the D-Ratio must be calculated by Year and Division

e.g. D-Ratio for HR in 2010 = 0.6031

 Year Division Nationality S M P P*(1-P) D-Ratio 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 D-Ratio over time by Division by Level, then again D-Ratio must be recalculated at "Year-Division-Level" 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,

• ###### 1. Re: Complicated Dynamic Calculation - Need help from the Community

Hi Tuyen,

Create calculated fields as mentioned below

Step 1: Create a calculated field for year wise headcount

Step 2: Create a calculated field to compute the sum of the value of headcount per nationality

Step 3: Create a calculated field to compute P

Step 4: Create a calculated field to compute p*(1-P)

Step 4: Drag Year to Columns And  P*P(1-p) to rows and change the mak type to line

Step 5: Now you can see year wise D-ratio for 2010 it is 0.72753 and 2011 is 0.80474

Step 6: Drag Division to filter and add it to the context

Step 7: SHow the filter and select HR and value for 2010 is 0.6031

Hope this helps and by the way nice concept to work on thanks for it

Kindly mark this answer as helpful and correct so that it will help others

BR,

NB

• ###### 2. Re: Complicated Dynamic Calculation - Need help from the Community

Hi Naveen,

Thanks for your help, but I think this solution cannot solve the problem completely.

It works if I set the Division as Context filter and the graph only has 1 line.

Now if I set the Division as Color, I expect to see 3 different lines for HR, Finance and IT. However, with this, we still only see 1 line as the MDR was calculated by FIXED Year and Nationality only (Actually, there are still 3 lines but they have exactly the same values, so we only see 1 on the graph)

The challenge is how to create the dynamic MDR  calculation so that it changes when we drill-down by using another dimension as Color or Detail, etc.

• ###### 3. Re: Complicated Dynamic Calculation - Need help from the Community

Hi Tuyen,

Adding dimension to context will increase the granularity

Could you please give me an example or any snapshot of excel graph how you want so that it will be more helpful to decipher the problem

BR,

NB