How to use DAX to Sum sales and take the average per transaction?

[Anonymous]

I would like to use DAX to find the average transaction sales for each grouping type.

This is how I would do it in SQL. However, I'd like to do this for each grouping_type. I'd have to re-run this query for each grouping type and compute the average. I'd rather not do that.

How would I implement this in DAX? Is this possible?

SELECT 
	transaction_key
	,SUM(sales) sum_of_sales
INTO #y
FROM #x x
WHERE x.transaction_key IN (SELECT DISTINCT transaction_key FROM #x x1 wHERE x1.grouping_type=6)
GROUP BY transaction_key

SELECT AVG(sum_of_sales)
FROM #y

period_type	Store	store_type	transaction_date	transaction_key	department	ItemLookupCode	grouping_type	sales
A	1504	P_STORE	7/6/2017	1504-1567118	BE	120733	6	13.49
A	1504	CONTROL	6/10/2017	1504-1567118	BE	780549	4	12.99
D	1504	CONTROL	8/9/2017	1504-1567118	BE	788755	6	15.99
A	1536	CONTROL	6/17/2017	1536-947058	SP	67	1	24.95
C	1536	P_STORE	7/11/2018	1536-1073185	BE	691097	12	26.79
D	1549	P_STORE	8/3/2017	1549-1073185	SP	110056	1	25.95
A	1515491	CONTROL	6/17/2017	1549-623376	BE	755990	6	15.99
C	187	CONTROL	5/24/2018	187-347592	BE	688549	30	45.95
A	187	CONTROL	6/14/2017	187-347592	BE	733500	1	2.99
A	187	CONTROL	6/30/2017	187-347592	BE	790513	12	22.99
C	187	P_STORE	6/6/2018	187-329148	SP	367672	1	29.95
C	5819	CONTROL	6/6/2018	5819-329148	BE	802295	12	29.99
C	5819	CONTROL	6/12/2018	5819-264390	BE	732937	6	19.49
C	5819	CONTROL	5/24/2018	5819-264390	WI	773064	1	11.99
C	5912	CONTROL	5/18/2018	5912-264390	BE	724952	15	25.79
D	7348	P_STORE	8/3/2018	7348-264390	WI	774932	1	10.19
D	7348	P_STORE	8/9/2018	7348-264390	BE	776182	1	4.29
D	7348	CONTROL	8/4/2018	7348-1113160	BE	749283	24	33.29

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may try to use SUMMARIZE Function. For example:

Measure  =
VAR b =
    SUMMARIZE ( Table2, Table2[transaction_key], "a", SUM ( Table2[sales] ) )
RETURN
    AVERAGEX ( b, [a] )

Regards,

Cherie

[Anonymous]

I would like to use DAX to find the average transaction sales for each grouping type.

This is how I would do it in SQL. However, I'd like to do this for each grouping_type. I'd have to re-run this query for each grouping type and compute the average. I'd rather not do that. The tricky part is that each transaction can contain multiple grouping_type and must be considered in the calculation.

How would I implement this in DAX? Is this possible?

SELECT 
	transaction_key
	,SUM(sales) sum_of_sales
INTO #y
FROM #x x
WHERE x.transaction_key IN (SELECT DISTINCT transaction_key FROM #x x1 wHERE x1.grouping_type=6)
GROUP BY transaction_key

SELECT AVG(sum_of_sales)
FROM #y

period_type	Store	store_type	transaction_date	transaction_key	department	ItemLookupCode	grouping_type	sales
A	1504	P_STORE	7/6/2017	1504-1567118	BE	120733	6	13.49
A	1504	CONTROL	6/10/2017	1504-1567118	BE	780549	4	12.99
D	1504	CONTROL	8/9/2017	1504-1567118	BE	788755	6	15.99
A	1536	CONTROL	6/17/2017	1536-947058	SP	67	1	24.95
C	1536	P_STORE	7/11/2018	1536-1073185	BE	691097	12	26.79
D	1549	P_STORE	8/3/2017	1549-1073185	SP	110056	1	25.95
A	1515491	CONTROL	6/17/2017	1549-623376	BE	755990	6	15.99
C	187	CONTROL	5/24/2018	187-347592	BE	688549	30	45.95
A	187	CONTROL	6/14/2017	187-347592	BE	733500	1	2.99
A	187	CONTROL	6/30/2017	187-347592	BE	790513	12	22.99
C	187	P_STORE	6/6/2018	187-329148	SP	367672	1	29.95
C	5819	CONTROL	6/6/2018	5819-329148	BE	802295	12	29.99
C	5819	CONTROL	6/12/2018	5819-264390	BE	732937	6	19.49
C	5819	CONTROL	5/24/2018	5819-264390	WI	773064	1	11.99
C	5912	CONTROL	5/18/2018	5912-264390	BE	724952	15	25.79
D	7348	P_STORE	8/3/2018	7348-264390	WI	774932	1	10.19
D	7348	P_STORE	8/9/2018	7348-264390	BE	776182	1	4.29
D	7348	CONTROL	8/4/2018	7348-1113160	BE	749283	24	33.29