11 Replies Latest reply on Apr 19, 2018 5:12 PM by Zhouyi Zhang

    Distinct Sum of Measure based on Dimensions

    Stacy Joannes

      I have what seems to be an easy question that I cannot get exactly right.

       

      What I have:

      • I have a value in a field called "How Attached" where the value is either "Network" or "Local".  So far this is simple - right?
      • In the same table I have a field called "Serial Number".
      • Each Serial Number is listed several times with a value of how attached.  I realize this could be better normalized (that is another topic for another day)

       

      So what is the problem?

      • I can very easily get total pages by district. Check. No problem.
      • I can drag "how attached" to rows or columns and it will give me a grouping of records by "how attached." Check. No problem.
      • What I can't get is a simply sum of "Total Networked" and "Total Local" in a text table. (see the output I want below).

       

      What have I tried?

      • I assume I need to make a calculated field. Right?
      • I need a distinct count of "total networked" and "total Local" by serial number/District.  This seems so simple. I've tried countless calculation fields.

       

      What do I have to give if you solve this?

      • I don't have much.  I'm an expert Crystal Reports person that loves the amazing power of Tableau.  I'm in the process of converting many reports from Crystal to Tableau.
      • I can offer you golf advice, but only if you are really bad and just need help breaking 100.
      • I can tell you my favorite cities in the world include: Lauterbrunnen (Switzerland), Rome (Italy), San Diego (California), Madison (Wisconsin), Nashville (Tennessee) and New York.

       

      What Output I Want (and cant get in a text table)

      DistrictTotal PagesTotal NetworkedTotal Local
      Wisconsin4431
      Illinois3411
      Texas810

       

       

      What My table Looks Like:

      aae123 1 aae123 2

      Network

      DistrictSerial NumberHow AttachedPagesDocument
      Wisconsinaae123Network12Apples.ppt
      Wisconsinaxx129Network16Flower bee combo.xls
      Illinoishgs331Local18Pears are us.xls
      Illinoisjhs331Network16Pear production schedule.xls
      Wisconsinabf455Local14Apple next year.ppt
      Texastxe123Network8Apple without fert.xls
      Wisconsinaae123Network1Appl.doc
      Wisconsinxxd123Network1Applecv3.pdf
        • 1. Re: Distinct Sum of Measure based on Dimensions
          Zhouyi Zhang

          Hi, Stacy

           

          something like this? If yes, please find attached sample workbook

           

           

          Hope this helps

           

          ZZ

          • 2. Re: Distinct Sum of Measure based on Dimensions
            Jim Dehner

            Hi

            Like your sense of humor and live in Nashville and love Madison, past resident of San Diego and in switzerland is Luzern  for me 

             

            ok that said the chart below is one LOD formula and grand totals

             

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

             

             

            • 3. Re: Distinct Sum of Measure based on Dimensions
              Stacy Joannes

              ZZ: Thank you for the reply.  Your post was close.  The part I still cannot get is that I need a distinct # for the district by serial number.  For example: Wisconsin as 5 records, however, I only want to count distinct serial #'s  once.  So, aae123 is listed twice (but should only count as 1).  I want the sum of each pages, but I only want to count the serial # once.   Yours gave me the sum, but I need the distinct district serial #'s for "Local" and distinct district serial #'s for "Network".

               

              Example:  Lets say serial # AAE123 printed 5 documents.  I want total pages for all print jobs (that part is done and easy).  For my totals, I only want that printer to add 1 to the Network count.

              • 4. Re: Distinct Sum of Measure based on Dimensions
                Stacy Joannes

                Jim - Unless I'm not understanding, I can't use this as is to go along with other measures? I need one measure that is the total distinct district serial numbers that are "Network"  and another field just for local.  So in my table if I have a serial number aae123 (from Wisconsin) that printed 5 documents, I want to total pages (easy to get that, and that is not the solution I'm looking for).  I want to show that for Wisconsin distinct serial numbers designated as network or local.  In this example, aae123 would add one to the total for Wisconsin for network,  If another serial number printed (from Wisconsin) it would add one to the total assuming it was not aae123.  This seems so easy.  Manybe I'm not explaining right.

                • 5. Re: Distinct Sum of Measure based on Dimensions
                  Zhouyi Zhang

                  Hi, Stacy

                   

                  please find my calculation below. In the sample data you shared can't reflect your real case, but try below calculation and let me know whether it works or not.

                   

                   

                  Hope this helps

                   

                  ZZ

                  • 6. Re: Distinct Sum of Measure based on Dimensions
                    Stacy Joannes

                    ZZ:  Oh, this is so exciting.  Can you show me what you used in the AGG(Total Network) and AGG(Total Loc) measure calculations?  That should get me the final solution.  That is the part I cannot figure out.  With your path, I think this will work if I can see that.  Thank you for helping, so greatly appreciated.

                    • 7. Re: Distinct Sum of Measure based on Dimensions
                      Zhouyi Zhang

                      Hi, Stacy

                       

                      In the workbook I shared last time, the calculation fields were already there, please go to your original thread and find my sample workbook.

                       

                      Below is the screenshot of these two for your reference as well.

                       

                       

                      ZZ

                      • 8. Re: Distinct Sum of Measure based on Dimensions
                        Stacy Joannes

                        ZZ: You are the best for trying to help.  I want to make this simple.  I have created a simple DB below.  The one I did originally maybe didn't show the issue.  If you look below for Wisconsin the output should be 1 and 3.  That is the number I can't get.  The pages printed doesn't even matter until I get the first part fixed.  Since DeviceID 123 printed 4 times, it is counting as 4 (that is the problem).  I want it to count as 1.  So when I look at millions of records, I can see that Wisconsin had "X" amount of local printers and "X" amount of network printers.  In our example below I should get 1 local (124) and 3 network (123, 125 and 126).   Again, I can do this very easy with a group or chart, but need the numbers to be in a text table as shown below.  Thank you again so much.  This seems so incredibly easy, but I cannot figure this out.

                         

                            

                        DistrictDeviceIDPrintedHowAttached
                        Wisconsin123312Network
                        Wisconsin1231231Network
                        Wisconsin12312Network
                        Wisconsin1231Network
                        Wisconsin1242Local
                        Wisconsin1244343Local
                        Wisconsin125232Network
                        Wisconsin1262Network
                        Florida12723Network
                        Florida1282Network
                        Florida1292Network
                        Michigan13043Network
                        Michigan13123Local
                        DistrictLocal NetworkTotal Pages
                        Wisconsin136135
                        Florida0225
                        Michigan1166
                        • 9. Re: Distinct Sum of Measure based on Dimensions
                          Zhouyi Zhang

                          Hi, Stacy

                           

                          Please find my updates attached and below screenshot

                           

                           

                          In your new sample data, I think Florida should be 0 for local and 3 for network.

                           

                          ZZ

                          • 10. Re: Distinct Sum of Measure based on Dimensions
                            Stacy Joannes

                            ZZ:

                             

                            YEAH!  It worked.  After this fix I was able to create about 10 other calculations with the same principle.  The solution seems so easy, but I really needed the ability to have as a value. So THANK YOU 100000x over.  I was able to finish and match 90% of my Crystal Report with this.  Thank you for sticking with me.  I also will look at the "fixed" feature.  Seems like that should have worked as well.  I hope I can repay one day.  Have a super day.

                            • 11. Re: Distinct Sum of Measure based on Dimensions
                              Zhouyi Zhang

                              Hi, Stacy

                               

                              You are welcome and I am glad to help you out

                               

                              Hope you will enjoy Tableau.

                               

                              Cheers,

                               

                              ZZ