tableau count non zero values in a row

Question

I have data in tableau like below

day       employee    expense_reason        expense
2/24/2018   abc   car mileage               $5 
2/24/2018   xyz   car mileage               $5 
2/24/2018   xyz   car rent                  $8 
2/24/2018   xyz   car rent                  $9

I want to find occasions when on a same day, single employee claims both expenses. I am producing below view in Tableau using day and employee as rows expense reason as column and sum(number of rows) as Text

i am getting grand total using analysis>>totals>>show row totals instead of getting row totals, how could i get count of non zero values in a row

                  expense_reason        
day        employee   car mileage   car rent    grand total
2/24/2018   abc         1                         1
2/24/2018   xyz         1           2             3

update 1

i tried below as per one of the answers but it is not giving output that i want

could you show an example of ZN function - how could i use it in the context of this example? i will research more too — Ni_Tempe

Fabio Fantoni Fabio Fantoni · Accepted Answer · 2018-02-26T17:22:30

First of all you should solve the No-Data part since there's no data for the combo abc-rent.

You can achieve this using the lookup function, forcing a fake lookup using 0 as parameter. Something like this:

LOOKUP(SUM([Something]),0)

Then you can use ZN function to force a zero for a null value, which is different from No Data.

That being said, you should be able to get something like this:

Here's the Calculated field:

zn(LOOKUP(max({ FIXED [day],[employee],[reason] : COUNT([reason])}),0))

EDIT

If you want to have grand totals, i think this should be the better solution:

And the Calculated Field is a pure Count nested in a lookup function as said before:

zn(LOOKUP(COUNT([expense]),0))

tableau count non zero values in a row

update 1

1 Answers