12 Replies Latest reply on Jun 25, 2019 9:38 AM by Anuvir Singh

# How do I calculate ratio with 2 different domains in a dimension when using a calulated field?

Hi!

I'm new to tableau trying to calculate the ratio of false alarms to fires but I keep getting errors. I first started by summing up all the incidents in "Total incidents" in a calulated field. Then I created 2 separate clauses for my ratio calculations as follows:

IF ATTR([Callout_type]) = "ALL FALSE ALARMS" THEN [Total Incidents] END

and

IF ATTR([Callout_type]) = 'ALL FIRES' THEN [Total Incidents] END

and finally the ratio calculation:

SUM([All False Alarms])/SUM([All Fires]) but I keep getting an error.

I think the issue arises because "Total incidents" is a calculated field and I cannot aggregate this further but I'm not sure how to go about this. Any help would be appretiated! I have attached my workbook for you to have a look

• ###### 1. Re: How do I calculate ratio with 2 different domains in a dimension when using a calulated field?

Hi Eugene, can you post a v10.5 please ?

File > Export as version...

Peter

• ###### 2. Re: How do I calculate ratio with 2 different domains in a dimension when using a calulated field?

Hi Eugene,

It is showing the connection error,

Thanks,

Kalpit

• ###### 3. Re: How do I calculate ratio with 2 different domains in a dimension when using a calulated field?

Hi Eugene,

Try the following formula for All False Alarms and similar for All Fires.

sum(IF ([Callout_type]) = "ALL FALSE ALARMS" THEN {fixed [Callout_type]:[Total Incidents]} END)

• ###### 4. Re: How do I calculate ratio with 2 different domains in a dimension when using a calulated field?

Here you go!

• ###### 5. Re: How do I calculate ratio with 2 different domains in a dimension when using a calulated field?

Hi Kalpit,

Here is the SHP file I used

• ###### 6. Re: How do I calculate ratio with 2 different domains in a dimension when using a calulated field?

I don't seem to be returning the correct values when using this formula and I still get the aggregation error

• ###### 7. Re: How do I calculate ratio with 2 different domains in a dimension when using a calulated field?

I relase I didn't extract the data when I attached my workbook. Here is the correct version!

• ###### 8. Re: How do I calculate ratio with 2 different domains in a dimension when using a calulated field?

Attached is the updated file. Let me know if this works for you. I have changed the False Alarms per fire formula. Check it out.

Thanks,

Anuvir.

• ###### 9. Re: How do I calculate ratio with 2 different domains in a dimension when using a calulated field?

Thank you for your reply I do seem to get the correct ratio when I use the formula, however my intention is so that the ratio reflects the bourough(s) filtered. At the moment it seems that the ratio calulation stays the same.

• ###### 10. Re: How do I calculate ratio with 2 different domains in a dimension when using a calulated field?

Try this formula

sum(IF ([Callout_type]) = "ALL FALSE ALARMS" THEN {fixed [Callout_type],[Borough]:[Total Incidents]} END)

i have added Borough to the Formula. This should work for filtering by Borough.

• ###### 11. Re: How do I calculate ratio with 2 different domains in a dimension when using a calulated field?

yes thank you! This is what I was looking for. If you don't mind could you explain your formula and why it works so I can understand it?

• ###### 12. Re: How do I calculate ratio with 2 different domains in a dimension when using a calulated field?

Sure so we can read this formula as follows- for each Callout_Type and Borough compute the total incidents.

why did i use fixed in this

1)  In the Calc -Total incidents is an aggregate and callout type is a non aggregate. In order to use them together both of them should be at the same level either aggregate or non aggregate. So in order to convert a measure into a non aggregate from an aggregate we use a LOD.

2) when using the calc we have to keep in mind what level do we want the calc to be on. so we fix them on Callout_Type and Borough.

Attached is a good link that you can refer if you want more info on LOD calc.