7 Replies Latest reply on Apr 9, 2018 8:50 AM by ShivaRam Chennapragada

How do I calculate ratios using the data in two columns to create a ratio?

I am a Tableau "newbie" and I
have a sheet of data with multiple columns in which I want to divide column A
by column B to obtain a ratio e.g. and then format it into a percentage.

I created a calculation (A/B) and when I
add the calculation to be a new column all I'm getting is a whole number e.g
247.

Can you help me to determine what I'm doing
wrong?

• Below is a clip of my sheet in which I want
to divide the following columns to get a ratio.

16-Losses Paid 2017 1 / 11-Premium 2017 to
get a ratio of

1. e.g.

1,391,737 / 1,041,539 = 1.33 or 133%

The two columns are 16-Losses Paid divided by 11-Premium which should be displayed in a created column called Loss Ratio.

• 1. Re: How do I calculate ratios using the data in two columns to create a ratio?

If you need the result in %, create a calculated field 'Ratio' > SUM([A])/SUM([B]). Click 'OK'. You should now see it under Measures, right-click > Hover over 'Default Properties' > Select 'Number Format' > Select 'Percentage' and Decimal Places you want. Attached screenshots,

Hope this helps.

Thanks,

Shiva.

• 2. Re: How do I calculate ratios using the data in two columns to create a ratio?

ShivaRam;

Thank you for the screen shots and attached.

I am a “New” Tableau user.

Ideally, I would like to show both the numerator for all rows e.g.

Bill

• 3. Re: How do I calculate ratios using the data in two columns to create a ratio?

Like this?

• 4. Re: How do I calculate ratios using the data in two columns to create a ratio?

Exactly!

Here’s the what the Sheet looks like.

[cid:image003.png@01D3CFF4.91FE5350][cid:image001.png@01D3CFF3.D82BD470]

The 1 and 3 under 2017 refers to 1 = Private Passenger Cars and 3 = Commercial.

Here how the it would work.

1 (Private Passenger Cars) 16-Losses Paid 1,391,737 would be divided by 1 (Private Passenger Cars) 11-Premium 1,041,539 for a Loss Ratio or 1.33 Or 133%.

3 (Commercial) 16-Losses Paid 842,526 would be divided by 3 (Commercial) 11-Premium 272,641 for a Loss ratio of 3.09 or a Loss ratio of 309%.

I know I’ll have to create a calculation for Loss Ratio – Not sure how.

Bill

Sent: Monday, April 09, 2018 10:57 AM

To: Bill Benton

Subject: Re:  - How do I calculate ratios using the data in two columns to create a ratio?

How do I calculate ratios using the data in two columns to create a ratio?

• 5. Re: How do I calculate ratios using the data in two columns to create a ratio?

If I understand correctly, is this what you're looking for?

If so, refer to the workbook and find the 'Loss Ratio' calculations under Measures. Hope this helps.

Thanks,

Shiva.

• 6. Re: How do I calculate ratios using the data in two columns to create a ratio?

That’s it!

Where would I find the workbook?

• 7. Re: How do I calculate ratios using the data in two columns to create a ratio?

It should be under my previous reply,