10 Replies Latest reply on Jul 5, 2018 10:47 AM by Krista Ernewein

Counting sales <\$X without having to breakout the table by unique order ID

Hi -  I couldn't exactly decide how to title this but...

I have a bunch of orders that I want to plot on a map to indicate where all of our <\$99 sales are coming from and another map indicating where our \$99+ sales are coming from. So essentially a histogram but in map form. However, when I remove the Order ID granularity from my row shelf all of my individual sales get summed together and now the count of <\$99 sales is incorrect. I'm wracking my brain because this seems like an obvious task, but for some reason can't seem execute it.

How do I tally <\$99 individual sales at the unique ID row level (without breaking my data out by Row ID)?  i.e. if I have ten \$50 purchases, how do I get a count of 10 rather than a result of zero because Tableau summed purchases to \$500.

Again this feels painfully obvious, but I'm just not connecting the dots right now. Thanks in advance!

- Krista

• 1. Re: Counting sales <\$X without having to breakout the table by unique order ID

Hi Krista,

Thanks

Deepak

• 2. Re: Counting sales <\$X without having to breakout the table by unique order ID

Attached are two images to hopefully outline what I'm talking about. The first image (breakdown by order IDs) shows every single sale (row) sorted by State. There are multiple unique unit prices that recur and I'd like a tally for each by State, for instance:

CA:

1x \$109.65

14x \$99.00

5x \$74.25

2x \$24.95

1x \$0.00

When I remove the ID pill from the rows shelf (see image - breakdown by state only),  the unit prices all get summed together. This makes my custom filter (sales >\$99) inaccurate. Ultimately the goal is to count the number of sales that are over \$99 and similarly those < \$99 and map them. So I need this calculation to happen at the individual/row level, but I'm not sure how to do that.

Does this make sense?  Thanks for the help!

• 3. Re: Counting sales <\$X without having to breakout the table by unique order ID

Drag ID to Detail after Removing FRom Rows

• 4. Re: Counting sales <\$X without having to breakout the table by unique order ID

Attached is the result, but not exactly what I'm looking for. Was looking for more of a compact summary.

• 5. Re: Counting sales <\$X without having to breakout the table by unique order ID

Now This is at Calculation Level. We need to Embedded ID in your Calcs to get you There. You have some fake similar workbook?

• 6. Re: Counting sales <\$X without having to breakout the table by unique order ID

I've attached a sample to the initial post.

• 7. Re: Counting sales <\$X without having to breakout the table by unique order ID

So This is what You have:

I changed your Calculations and added a flag to check <99 or >99

<99

>99

Thnaks

Deepak

If it Helps, pl mark it Helpful and CORRECT to close Thraed

1 of 1 people found this helpful
• 8. Re: Counting sales <\$X without having to breakout the table by unique order ID

Yours is definitely doing what I want, however "Number of Purchases" in my actual workbook (not the sample I sent you) is a calculation = CountD(OrderID).  So because this is already an aggregate, the Sum([Number of Purchases]) from your Total Purchases calculation isn't valid.

• 9. Re: Counting sales <\$X without having to breakout the table by unique order ID

{Fixed A,B:COUNTD(ORDER ID)}

You may also Write