7 Replies Latest reply on Feb 28, 2018 1:41 PM by Eric Hammond

# Ratio display

Hi All,

I have 2 measures in the report and need to display the ratio of those measures  in a specific format as given below .How can I  implement this .Please help

• ###### 1. Re: Ratio display

Hi, Deepesh

Not sure what you are going to achieve from your sample workbook. can you explain more details?

ZZ

• ###### 2. Re: Ratio display

Hi Deepesh;

Are you trying to determine if your numbers match the given ratio?

If so, I might begin by reshaping your data: Split the Ratio into two fields: [Ratio - Split 1] and [Ratio - Split 2]. These are the default names Tableau will generate once [Ratio] is split on your GUI.

Next, a quick calculation:

Test Ratio

IF [A] * [Ratio - Split 2] = [B] * [Ratio - Split 1] THEN "Correct Ratio"

ELSE "IMPROPER RATIO"

END

If you are trying to GENERATE B from A, you can use a similar bit of logic:

B Ratio

[A]*[Ratio - Split 2]/[Ratio - Split 1]

Hope this helps-- Michael

• ###### 3. Re: Ratio display

I think he has the fields A and B and wants to build the ratio field in the 1:2 format in a tableau calc.

• ###### 4. Re: Ratio display

I havethe fields A and B and wants to build the ratio field in the 1:2 format in a tableau calc.

• ###### 5. Re: Ratio display

Interesting!

If you're trying to create the ratio, I think you'll need the GCD (Greatest Common Divisor) [or is it Denominator?].

Joe Oppelt had some words to that effect in this post:

How can I calculate the Greatest Common Divisor for two numbers?

Good luck! --Michael

• ###### 6. Re: Ratio display

Is it possible to implement  what Michael Hesser posted about Joel's idea with Python integration. It is not that difficult

• ###### 7. Re: Ratio display

If possible, help your audience to understand the benefits of viewing the relationship between A and B in decimal form instead of as a ratio.  If they insist on seeing a ratio then the following calculation may help:

STR(ROUND([A]/[A]

* IF ROUND(            ([B]/[A]) % 1                ,4) = 0 THEN 1 ELSE           (1 / (([B]/[A]) % 1))

* IF ROUND(      (1 / (([B]/[A]) % 1)) % 1          ,4) = 0 THEN 1 ELSE      1 / ((1 / (([B]/[A]) % 1)) % 1)

* IF ROUND((1 / ((1 / (([B]/[A]) % 1)) % 1)) % 1    ,4) = 0 THEN 1 ELSE 1 / (1 / ((1 / (([B]/[A]) % 1)) % 1) % 1)

END

END

END

,0))

+ ":" +

STR(ROUND([B]/[A]

* IF ROUND(            ([B]/[A]) % 1                ,4) = 0 THEN 1 ELSE           (1 / (([B]/[A]) % 1))

* IF ROUND(      (1 / (([B]/[A]) % 1)) % 1          ,4) = 0 THEN 1 ELSE      1 / ((1 / (([B]/[A]) % 1)) % 1)

* IF ROUND((1 / ((1 / (([B]/[A]) % 1)) % 1)) % 1    ,4) = 0 THEN 1 ELSE 1 / (1 / ((1 / (([B]/[A]) % 1)) % 1) % 1)

END

END

END

,0))

For ratios that are more difficult to resolve this returns a ratio that isn't exact.  For example, if A is 57 and B is 672, it returns 6:75 instead of 19:224.  For more granularity, add more IF statements following the pattern in the calculation; this will result in a correct ratio of 19:224 in the example given.

1 of 1 people found this helpful