Tableau creating calculated field using filter

1
votes

i have a table in tableau and i wanna create a calculated field based on a filter. Can someone help me in finding the solution for the following logical statement.

I have a table which contains some customers. It also contains some special customers say 'members'. Now I wanna show the total amount of billing for members customers and total amount for the remaining ones.

I have special range for these member customers ID. Ids lies between 300 and 399, both inclusive.

Please help. Thanks in advance

1
fieldtableau-apicalc
Can you provide more detail. You have a filter - which I assume is either a data field or a calculated field - and you want to create (another) calculated field based on that field? – Nick
here are the field names -Dimension (Customer code) Measure - (Customer Pay)...... These customer codes also consists of member customers whose customer codes are in the range 300 and 399. Now this is what i need to show on the same sheet - sum of Customer Pay (excluding members) and sum of customer pay (only members)......I just need the solution. Doesn't matter what approach i take. – Navy

1 Answers

1
votes

I assume that the measure you want to sum is the Customer Pay. Then, you can do this in three neat steps.

First Step: Create a calculated field called 'Customer Type'

IF [Customer Code] >= 300 AND [Customer Code] <= 399 THEN 'Member' ELSE 'Normal' END

Second Step: Create a calculated field called 'Member Total Billing Amount'

SUM(IF [Customer Type] = 'Member' THEN [Customer Pay] END)

Third Step: Create a calculated field called 'Non-member Total Billing Amount'

SUM(IF [Customer Type] = 'Normal' THEN [Customer Pay] END)

You can now drag Member Total Billing Amount and Non-member Total Billing Amount measures into the view as desired.

Note:

  1. The calculated field called 'Customer Type' will be saved under Dimensions. So, go up there and look for it.

  2. I have only named the calculated fields for illustration purposes. Feel free to change it to what is more intuitive for you.

  3. Another thing to consider, depending on your needs, will be to add Customer Type on the Color Marks card and it will divide Customer Pay into 'Members' and 'Normal'.