If i understand correctly you have more than one row with the same Customer_id -Receipt_ID combination and each combination always have the same value in the Total column.
So what you want is to display the Total group by Customer_id , Receipt_ID
The logic would be to use the Attribute function ATTR which returns the value of the column Total if it is unique otherwise it will return a *. The problem is you wont be able to use the Grand Total on Attribute values.
But you can accomplish the same thing using a Measure aggregation like MIN.
So instead of aggregating all the Customer_id -Receipt_ID rows with the SUM of Total change the aggregation for the MIN of Total. Just right-click on SUM(total) and switch from Measure-Sum to Measure-Minimum.
The receipt_id’s are not duplicated for Customer_id 2.
The problem is that the Totals for each receipt_id are correct ONLY when it is expressed as a Dimension. When I select Total as a Measure it takes the total you see as a dimension and multiplies that total against the number of products found on that receipt_id.
So, the example of receipt_id 10241, is that the correct Total is £230.0. As an unaggregated Dimension (the first tab of the workbook), I can see that there are 9 instances of this Total, so 9 products on that receipt, which is why, when I express TOTAL as a Measure it multiplies 230.0 nine times.
I would like to see each receipt_id as a measure with the correct value, I can only see when it is a dimension.
So the output would be,
Receipt_id customer_id Total
10241 2 230.0
10243 2 1.5
10246 2 0
10320 2 230.0
10331 2 4
Hope this makes sense?
Well i don`t think it`s a Dimension vs Measure problem here.
If a Dimension, Tableau will return the discrete attribute of the column , so if you have a measure you can always set it to Discrete and Aggregate with ATTR and you will have a similar result.
First in your case, all you xxx_id should be retyped as String and change as a Dimension , and for Total since it`s a numerical value it`s a good thing to keep it as a Measure. Remember that you can always set a Measure to discrete values if you want to display a list of the values , instead of using the values to draw a Bar for example.
But if you need to make calculation on those values , it`s another ball game.
The problem in your case is that for every combination of consumer-receipt you have more than one row , one for each receipt_products_id , every row having the same Total. And since you need to bring in the view a single value of the Total per customer-receipt group , using SUM(Total) wont do the job . (And that`s why if you set Total as a Dimension you bring a single value of Total in the view , because Dimension values are discrete by default).
So you can use MIN(Total) or AVG(Total), since all Totals are the same for every receipt_products_id , the AVG or MIN will return the good Total value.
But i just saw a problem with the Grand Total in the attached i sent , instead of returning the SUM of the MIN it returns the MIN of MINs , and with the AVG it returns the AVG of the AVGs .
I tried to bring the receipt_products_id on the detail and using the if first()=0 technique to retrieve the Total but i was not able to find a correct partition to compute my calculation, so i am stuck here.
There is a good link on Grand Totals from Jonathan Drummey , you might find your solution there