3 Replies Latest reply on Sep 18, 2013 2:08 PM by Michel Caissie

    Total of monetary field

    Lee Hadden

      The attached workbook concerns itself with the number of supermarket til receipts we have for user number 2 (Customer_id) and the total (Total) of each of those receipts (Receipt_ID).


      In the first tab 'Receipt Total as a Dimension, I can see the receipt total for each receipt id, broken down by how many products are shown on that receipt e.g. Receipt_ID 10241 is made up of 9 products.


      In the second 'Receipt Total as a Measure (SUM), I can see the receipt total summed for each receipt.  What is happening with this is that the receipt total has been multiplied for each product on this receipt e.g. Receipt_ID 10241 we know has 9 products on it, here it is shown as a total of 9 times the receipt total as a measure - 9 x 230.0 = 2070


      What I want to see is Receipt_id 10241's Total as 230.0 as a measure.  That way I could begin to look at month-on-month trends, total averages by customer_id's etc.

        • 1. Re: Total of monetary field
          Michel Caissie

          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.



          • 2. Re: Total of monetary field
            Lee Hadden

            Hi Michel,




            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




            Total                                                                                465.5






            Hope this makes sense?

            • 3. Re: Total of monetary field
              Michel Caissie

              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

              Customizing Grand Totals – Part 1 | Drawing with Numbers