10 Replies Latest reply on Sep 1, 2013 3:35 AM by Ville Tyrväinen

# Inexperience with calculated fields

Good day,

Getting to grips with Tableau and wondered if anyone can help.

The attachment shows the total of each receipt, entitled 'Total' for each consumer id. What i would like to get to is a grand total of these receipt total for each consumer id.

e.g. consumer id 23590 has 2 receipts - £21.23 & £29.74, total should be £50.97.

I have used the analysis tab to get total and have switched the field 'Total' to a measure, but, do not get anything like £50.97.

Your assistance would be most welcome.

• ###### 1. Re: Inexperience with calculated fields

Hi Lee

You need to convert the total dimension to a measure. To do so, right-click on "# total" and select Convert to Measure. To know what the difference is between a measure and a dimension, I suggest you read Tableau's help.

Remove the total field from your lines shelf and drag the new "# total" who shoud now be in the Measures section to the Text box. (where it says ABC, 123) next to colors and size.

Tableau should automatically do a SUM of the field for you.

Hope it helps!

1 of 1 people found this helpful
• ###### 2. Re: Inexperience with calculated fields

Hey Edgar,

Many thanks for getting back to me.  Alas, viewing this, in my live connected data is the only way in which I can see there true values.  As soon as I follow your instructions I get £48k as the answer to consumer id 23590.

Thanks,

Lee

• ###### 3. Re: Inexperience with calculated fields

Hi Lee,

When you right click on the 48k and select View Data, choose the Underlying Data tab--do those numbers add up correctly?

-Tracy

• ###### 4. Re: Inexperience with calculated fields

Hi Tracy,

What I can see happening is that it is using receipt TOTAL for each individual product.  So where I have used the receipt TOTAL as a Dimension I see the correct receipt TOTAL.  When I switch this to a measure (I want to get the sum of each receipt TOTAL – where a consumer has more than 1 receipt), I get every individual item on the receipt(s) times by the receipt TOTAL, hence the 48K.

What I want is the sum of the distinct receipt TOTAL.

Hope that makes sense?

Regards,

Lee

• ###### 5. Re: Inexperience with calculated fields

Something like this?

• ###### 6. Re: Inexperience with calculated fields

Yes.

• ###### 8. Re: Inexperience with calculated fields

I do apologise for being a little dense.

Could you explain the steps?

• ###### 9. Re: Inexperience with calculated fields

Better instructions:

1. Duplicate "total" and convert the duplicate to measure.

2 . Drop "total (copy)" from measure shelf into text/data box

3. Select consumer_id and then right-click create hierarchy (You can also just keep consumer_id and total as they were in your workbook and skip parts 3 - 4, if you don't need option to drill up/down)

4. Move "total" under consumer_id hierarchy and move that hierarchy into row shelf

5. Right-click "total" in row shelf and click that mark away from "Show header"

Now there is no header for "total" column.

If you need it:

6. Change "total (copy)" to "Measure values"

7. Set filter for "Measure names" and select only "(total (copy)"

8. Move "Measure names" into column shelf

• ###### 10. Re: Inexperience with calculated fields

So for Grand totals you need some measure and level of detail you want to show. In your case you need a Receipt Id, but if you don't have one, then you can just use "total" from dimension shelf for same purpose and just hide the header.