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

# Distinct Sum of Measure based on Dimensions

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

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

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

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

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

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

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

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

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.

 District DeviceID Printed HowAttached Wisconsin 123 312 Network Wisconsin 123 1231 Network Wisconsin 123 12 Network Wisconsin 123 1 Network Wisconsin 124 2 Local Wisconsin 124 4343 Local Wisconsin 125 232 Network Wisconsin 126 2 Network Florida 127 23 Network Florida 128 2 Network Florida 129 2 Network Michigan 130 43 Network Michigan 131 23 Local District Local Network Total Pages Wisconsin 1 3 6135 Florida 0 2 25 Michigan 1 1 66
• ###### 9. Re: Distinct Sum of Measure based on Dimensions

Hi, Stacy

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

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

Hi, Stacy