CODEWITHSUNDEEP
tableau-apicalculated-field
Tar
Tar

Reputation: 5

Tableau table filter for one column only in table

I have a really basic question that I can't figure out. I need to create a table that has multiple calculated fields, but I need only one of the calculated fields to be filtered for a specific dimension value. For example, I have the following data set (dummy data) and I want to create a table that will include total clicks for both companies, but [cost per click] from one company only, company B.

DATA SET

Company| Clicks| $ Cost
------------------------
Comp A | 100   | $20
Comp B | 200   | $40

WHAT I'M LOOKING FOR

       CLICKS | COST/CLICK
TOTAL   300   |  $0.13

$0.13 comes from 40/300; $40 from company B and 300 clicks from both company A and B.

How do you create a table that has multiple calculations but with one of those calculations filtered on one dimension value only?

Upvotes: 0

Views: 8080

Answers (2)

Aron
Aron

Reputation: 775

This should get you in the right direction. Based on your question and your comment, you want to divide the cost by the TOTAL number of clicks in your dataset.

  1. Create a calculated field called "TotalClicks" and enter this formula window_sum(sum([Clicks])) // This formula will sum the clicks field for all rows
  2. Create a calculated field called Cost / Clicks and enter this formula sum([Cost]) / [TotalClicks] Add the Cost / Clicks field to the sheet and it should look like this

NOTE: If you need to partition / group your report, you may have to play around with this some. I don't use window functions within tableau very often since I usually handle the aggregation at the datasource level instead.

enter image description here

NOTE: Since you mentioned filtering, I will add this statement -- If you filter out any of your data, that data will not (cannot) be included in any calculated fields (To the best of my knowledge and experience, anyway). If you need to include that data (total clicks), I think the only option is to add that aggregated total to your dataset - otherwise, tableau can't calculate it if you are filtering it out.

Edit2: If you cannot change the underlying dataset, you could accomplish this by creating another datasource and joining it to your inital data source --

  1. Data > Add Datasource, add the datasource again and change the name so you can identify it
  2. Click Data > Edit Relationships. Click Custom and REMOVE any linked fields -- this will essentially produce a Cartesian join (every record in your first DataSource will have every reocrd from your second Datasource)
  3. Select the second datasource and create a calculated field (ClicksTotal_DS2) using the same window_sum function
  4. Select the first datasource and create a calculated field (named Cost / Clicks_DS2) using this formula sum([Cost])/ [Sheet1 (test) (2)].[ClicksTotal_DS2]

Now you can apply a filter to your first data source, and your second data source will still calculate the total. enter image description here

Upvotes: 0

Bernardo
Bernardo

Reputation: 3318

One simple calculated field: 

sum(if [Company] = 'B' then [Cost] end )/sum([Click])

Upvotes: 1

Related Questions