1 of 1 people found this helpful
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!
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.
When you right click on the 48k and select View Data, choose the Underlying Data tab--do those numbers add up correctly?
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?
I do apologise for being a little dense.
Could you explain the steps?
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