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.

           

          Michel

          • 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

               

              Michel