Summing the Results of Grouped Table

[cjf4]

What I'm trying to do is create the equivalent of a GROUP BY SQL Statement as a virtual table, and sum the columns of that table.

Example:

I have data similar to the following table, and I would like to know:

• The # of Unique Customers with a sale in a given week
• The # of Unique Customers with multiple sales in a given week

sales_table:

Week	Customer	Sale
1	1111	1
1	2222	1
1	3333	1
1	3333	1
1	1111	1
2	1111	1
2	2222	1
2	3333	1
2	3333	1
2	1111	1

I can accomplish both of these for a given week with the following expressions.

UniqueCustomers

calculate(DISTINCTCOUNT(sales_table[Customer]),
	summarize(sales_table, 
		sales_table[Week],
		sales_table[Customer],
		"sales",
		count(sales_table[Customer])))

FrequentCustomers

calculate(DISTINCTCOUNT(sales_table[Customer]),
	filter(summarize(sales_table, 
			sales_table[Week],
			sales_table[Customer],
			"sales",
			count(sales_table[Customer])),
		[Sales]>1)

Both of these work as expected, returning the following for individual weeks:

	Unique	Frequent
Week 1	3	2
Week 2	3	2

The Problem:

When I want to evaluate this over the 2-week period, I want there to be 6 unqiue sales, and 4 frequent sales. In essence I am looking at each week as self contained, and only care about the number of unique customers in that period. 

Instead, when I do the calculations over the two week period, I get 3 unique sales, and 3 frequent sales.

Mental solution

In my head, I know I just need to sum the results of the intermediate group by table. However, after searching/reading the function defs/experimenting, I haven't been able to do so. Would appreciate any help.

[Anonymous · Accepted solution]

Not sure if it could help. You can create two columns and get the following tables.

Unique = CALCULATE(DISTINCTCOUNT(Sheet1[Customer]),ALLEXCEPT(Sheet1,Sheet1[Week]))

Frequent = CALCULATE(SUMX(Sheet1,Sheet1[Sale]),ALLEXCEPT(Sheet1,Sheet1[Week],Sheet1[Customer]))

Then in Report, you can add a table with week, unique and frequent fields. Remember to change these 3 values to Don't summarize, and add a filter for Frequent to filter out values that greater than 1.

[v-chuncz-msft]

@cjf4,

You may try a similar way as shown here.