# Sum of Distinct Count Variance

Options
Member

Hi

I need help on below beast mode not working.  I am trying to get the sum of all the variance using distinct count of customers.  I am trying to get my total points of distribution gained. Thank you in advance.

SUM(COUNT(DISTINCT
CASE WHEN `Year`= 2020 AND `Month #` <=`Month # for Today`
THEN `Customer Name`
END
))
-
SUM(COUNT(DISTINCT
CASE WHEN `Year`= 2019 AND `Month #` <=`Month # for Today`
THEN `Customer Name`
END
))

## Best Answer

• Coach
Answer ✓
Options

So ... while window functions are amazing for many things, it won't work when you're trying to do a count distinct.

why?

SUM(

COUNT(DISTINCT

CASE WHEN `Year`= YEAR(CURRENT_DATE()) AND `Month #` <=`Month # for Today` THEN `Customer Name`
END
))
OVER ()

keep in mind what is happening under the hood.  first Domo does a COUNT(DISTINCT) of customer names, for whatever is on the Axis.  (set of items)

then it adds up all the results.

so if i had item 1 = 15 distinct customers, item2  = 12 distinct customers and item3 = 31 distinc customers, i can't just sum 15,12,31... because presumeably i have overlap in the customers who bought items.

SOLUTION

APPEND your data onto itself (UNION) but change the Item number in the second UNION to be a constant 'Total Items'.

now you can do a basic count(distinct customer) and you'll have a 'Total Items' ... item.

Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨‍💻

**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

## Answers

• Coach
Options

You might be able to solve your problem using a windowing function however I'd recommend restructuring your data. I've done a previous write up regarding customized period over period data models. You can refer to https://dojo.domo.com/t5/Card-Building/show-percent-on-period-over-period-graph/m-p/50540/highlight/... for an in dept description of what needs to happen.

The basics being you'd have a period type column for Current and Last Year (which you'd filter on just these two) which you can then use a beast mode to calculate the total for that period and then subtract the two numbers. I'd highly recommend this method as it gives you a lot more flexibility in your analysis.

If you must use a beast mode you could try something like:

```SUM(COUNT(DISTINCT
CASE WHEN `Year`= YEAR(CURRENT_DATE()) AND `Month #` <=`Month # for Today`
THEN `Customer Name`
END
))
OVER ()
-
SUM(COUNT(DISTINCT
CASE WHEN `Year`= YEAR(CURRENT_DATE())-1 AND `Month #` <=`Month # for Today`
THEN `Customer Name`
END
))
OVER ()```

I've also replaced 2020 and 2019 with YEAR(CURRENT_DATE()) functions to automatically calculate the current year and last year (hence the -1) so you don't have to worry about changing the year value next year and in future years. (Unless you specifically want 2020 and 2019 then use those values)

**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
• Member
Options

Thank you for the response Grant.

I tried the BM you suggested. unfortunately it gives me a different result.  Please review the table below.  I am looking to get the FY20-FY19 variance both by item and as a total.  I hope you can help me figuring this out.  Thank you in advance.

 Item Number FY20 Customer Count FY19 Customer Count BM result Should be result 1012B 96 84 89 12 1042B 91 83 89 8 1044 90 84 89 6 1022B 80 69 89 11 1014B 67 64 89 3 1092B 58 52 89 6 Total 482 436 534 46
• Coach
Answer ✓
Options

So ... while window functions are amazing for many things, it won't work when you're trying to do a count distinct.

why?

SUM(

COUNT(DISTINCT

CASE WHEN `Year`= YEAR(CURRENT_DATE()) AND `Month #` <=`Month # for Today` THEN `Customer Name`
END
))
OVER ()

keep in mind what is happening under the hood.  first Domo does a COUNT(DISTINCT) of customer names, for whatever is on the Axis.  (set of items)

then it adds up all the results.

so if i had item 1 = 15 distinct customers, item2  = 12 distinct customers and item3 = 31 distinc customers, i can't just sum 15,12,31... because presumeably i have overlap in the customers who bought items.

SOLUTION

APPEND your data onto itself (UNION) but change the Item number in the second UNION to be a constant 'Total Items'.

now you can do a basic count(distinct customer) and you'll have a 'Total Items' ... item.

Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨‍💻

**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"