7 Replies Latest reply on Aug 22, 2018 1:47 PM by Ken Flerlage

# Count Rows based on same value(s) found in 2 columns

I have a dataset with 2 columns:

A    B   Balance

18  26    25

26  25    50

26 17     75

19 26     50

29 18     100

I want to be able to create a pivot table where I can group (and count, sum etc)  on a calculated field which treats values in column A or B as the same. For example, resulting pivot will look like the following:

AorB      count  sum(Balance)

17              1         75

18              2         125

19              1          50

25              1          50

26              4          200

• ###### 1. Re: Count Rows based on same value(s) found in 2 columns

You can do that with a union. Drag your table into the data pane, then drag it over top of it again to create a union. It'll look something like this:

Then create a calculated field like this (make sure to convert it to a dimension, if it's appearing as a measure):

AorB

IF [Table Name]="Sheet1" THEN

[A]

ELSE

[B]

END

Then you can do your aggregation:

See the sample data and workbook attached.

1 of 1 people found this helpful
• ###### 2. Re: Count Rows based on same value(s) found in 2 columns

Or just pivot your original data to put the A and B columns together.

Dan

1 of 1 people found this helpful
• ###### 3. Re: Count Rows based on same value(s) found in 2 columns

Haha...(insert facepalm here). Yeah, that would be way easier!

Yasir, if your data is in Excel or text, you can use a simple pivot. If it's in another data source, it gets a bit tricky. You could use the union approach given above, use Tableau Prep, or do a pivot with custom SQL. Here's a blog I wrote on it some time back, which gives a few different options: 3 Ways to Pivot Data for Tableau - Ken Flerlage: Analytics Architecture, Strategy, & Visualization

• ###### 4. Re: Count Rows based on same value(s) found in 2 columns

Fantastic Ken. Much Appreciated. Follow up, if you don't mind:

Now I want to join this self-union table (Sheet1+) with another table that has the mapping for AorB. The other file looks like the following:

Entity   Name

17- Acme Corp

18 - Banta Corp

19 -Charlie Corp

25 - BBC

26- CNN

29- ABC

The Final output will look like the following then:

Name  Balance

Acme Corp  75

Banta Corp 125

Charlie Corp 50

BBC 50

CNN 200

ABC 100

• ###### 5. Re: Count Rows based on same value(s) found in 2 columns

Well, you can't use an existing calculated field in a join, so you can't join it to AorB, but you can use a join calculation. So my suggestion would be to add in your Entities table.

For the join, choose to create a join calculation, then put just use the same calculation again:

And on the right side, of course, you can use the Entity ID

Then you'll get this in the end:

See attached.

1 of 1 people found this helpful
• ###### 6. Re: Count Rows based on same value(s) found in 2 columns

This is amazing.

Ken, you rock !!!

• ###### 7. Re: Count Rows based on same value(s) found in 2 columns

No problem Yasir