Equally distribute expenses to agents

[arnabmit]

I have accounts in the attached files, as in the first table. I need to equally distribute the unallocated cost for the month to each agent as Addl. Expenses.

I am trying to create the second table using SUMMARIZECOLUMNS. I have the formula working in excel, but I am lost trying to replicate it in DAX.

=SUMIFS($D$2:$D$19,$B$2:$B$19,B25,$A$2:$A$19,"Unallocated")/COUNTA(UNIQUE(FILTER($A$2:$A$19,($B$2:$B$19=B25)*($A$2:$A$19<>"Unallocated"))))

Excel 

Thanks for any help in advance!

[SpartaBI · Accepted solution]

@arnabmit 

New_Table = 
VAR _original_tbl = 
	FILTER(
		'Table',
		'Table'[Agent] <> "Unallocated"
	)
VAR _additional_expense = 
	CALCULATETABLE(
		ADDCOLUMNS(
			VALUES('Table'[Month]),
			"@Unallocated_Amount", CALCULATE(SUM('Table'[Amount]))
		),
		'Table'[Agent] = "Unallocated"
	)
VAR _new_tbl = 
	ADDCOLUMNS(
		SUMMARIZE(
			FILTER(
				'Table',
				'Table'[Agent] <> "Unallocated"
			),	
		'Table'[Agent],
		'Table'[Month]
		),
		"Type", "Addl. Expenses",
		"Amount", DIVIDE(
					SUMX(FILTER(_additional_expense, 'Table'[Month] = EARLIER('Table'[Month])), [@Unallocated_Amount]),
					CALCULATE(DISTINCTCOUNT('Table'[Agent]), REMOVEFILTERS('Table'[Agent]), 'Table'[Agent] <> "Unallocated")
				)
	)
RETURN

	UNION(
		_original_tbl,
		_new_tbl
	)

Showcase Report – Contoso By SpartaBI

     

[arnabmit]

Thank you so much! This worked perfectly! Now to try and learn more of DAX 😅

[SpartaBI]

@arnabmit my pleasure 🙂