1 Reply Latest reply on Apr 18, 2016 9:42 AM by Ashish Singh

# Calculated Field: How to split total amount 50/50 between two categories

Greetings!

I have a calculated field already setup in an existing dashboard. It buckets out money raised for certain groups of funds based on the categories they fall into. It's just an ELSEIF ENDSWITH statement. The problem I have is some of these funds should be split 50/50 between two categories with half of the total amount being counted in one group, and half in the other. I'd like to find a way to write this into the existing calc field if possible. Does anyone know of a way to do that?

Thank you!

Joelle

IF ENDSWITH ([Designation], '1234') THEN 'Fund Group 1'

ELSEIF ENDSWITH ([Designation], '5678') THEN 'Fund Group 2'

ELSEIF ENDSWITH ([Designation], '4321') THEN 'Fund Group 3'

ELSE 'Other Fund Group'

END

• ###### 1. Re: Calculated Field: How to split total amount 50/50 between two categories

It somewhat also depends upon after calculating this field, how do you want to 'display' it in the view.

Here is something I am attempting, hope this works for you

Problem

There is a group named group_1 which has raised amount 2x for any given period (this information is coming from day to day transactions table)

Also, group_1 falls into 2 categories cat_1 and cat_2 (and this information is stored somewhere in the master data)

Objective

In the group_1, each category cat_1 and cat_2 should show amount raised x even if the amount is not being raised against any particular category but for the whole group as one.

Concept

• If somehow I could 'group by' my view by groups
• And count distinct(categories) in each of those groups
• And divide the amount raised by group by number of categories
• Anf 'group by' that amount by categories and show it

Solution

There would a little data prep before being able to build the view

Data should be in the following format

group_01 has raised \$30K and group_02 has raised \$45K but we have to use duplicate that amount

Load the data in Tableau and write following two calculations

amtRaisedbyGroup

{ FIXED [Group]: AVG([Amount Raised])}

amtRaisedbyCat

AVG({ FIXED [Group],[Catergories]:

AVG([amtRaisedbyGroup] /

{ FIXED [Group]: COUNTD([Catergories])})

})

This way you can calculate both : amount by group, and average amount per category in each group

To understand how does this exactly work, you will require learning Level of Details topic, and lots of material is available online on that.

Hope this works. Please let me know if there is some problem.

P.S. I have created the .tabx in Tableau 9.3 and wouldn't open in older Tableau versions.

Cheers!

Ashish