using average and SUM

[zahrabajgiran]

INVOICE	STEP	INVOICEDATE	TASKENDTIME	DAYSSPENT
22093C	Process Invoice	2022-04-29	41:00.0	5
22093C	Super Approval	2022-04-29	14:00.0	37
22093C	GL Stamp Job/Subcontractor	2022-04-29	41:00.0	0
22093C	Final Approval	2022-04-29	41:00.0	0
22093C	PM Approval	2022-04-29	21:00.0	0
22093C	VP Approval	2022-04-29	22:00.0	0
22093C	Invoice Coder Not Found	2022-04-29	10:00.0	0
22113HB	Process Invoice	2022-05-17	52:00.0	93
22113HB	VP Approval	2022-05-17	31:00.0	7
22113HB	Super Approval	2022-05-17	48:00.0	28
22113HB	Final Approval	2022-05-17	09:00.0	0
22113HB	GL Stamp Job/Subcontractor	2022-05-17	53:00.0	0
22113HB	PM Approval	2022-05-17	55:00.0	28

Hello everybody, 

I am new to DAX and I searched a lot for my question but I was not successful to get an answer. 

I put my table here. 

I want to have a new coloumn which shows me the SUM of DAYSSPENT on unique INVOICE numbers, and then I want to be able to have an AVERAGE of those SUM.

[barritown · Accepted solution]

Hi @zahrabajgiran,

If the columns on the screenshot above match your expectations, you can achieve such results using two similar calculated columns:

SUM_DAYSSPENT = 
VAR CurrentInvoice = [INVOICE]
RETURN SUMX ( FILTER ( ALL(data), [INVOICE] = CurrentInvoice ), [DAYSSPENT] )

AVG_DAYSSPENT = 
VAR CurrentInvoice = [INVOICE]
RETURN AVERAGEX ( FILTER ( ALL(data), [INVOICE] = CurrentInvoice ), [DAYSSPENT] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian