Help with a calculated table

[Lharfenes]

Hi, I am in need of creating a calculated table based off other tables. 

Screenshot supplied above for clarity.

Thank you.

[Lharfenes]

Thank you so much for your interest in helping me!

Below is the link to the file. Please let me know if you are having issues accessing it.

Thanks in

https://hamaspikk-my.sharepoint.com/:f:/g/personal/lharfenes_hamaspikkings_org/EnRCip7LXj9Mvgv5Rnlf-...

[lbendlin]

sorry, the link asks for a login.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Lharfenes]

Can you send me your email and I will send you the file?

[lbendlin]

Sorry, not possible.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[Lharfenes]

Please try this new link.

https://hamaspikk-my.sharepoint.com/:f:/g/personal/lharfenes_hamaspikkings_org/EnRCip7LXj9Mvgv5Rnlf-...

Thank you for your patience.

[lbendlin]

The simplest way is to "borrow"  the DAX for the visual

// DAX Query
DEFINE
	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			ROLLUPADDISSUBTOTAL(ROLLUPGROUP('Date'[Date], 'CC'[Department]), "IsGrandTotalRowTotal"),
			"SumAmount", CALCULATE(SUM('Expenses'[Amount])),
			"DistinctCountName", CALCULATE(DISTINCTCOUNT('Employees'[Name])),
			"Expense_Per_Employee", 'Expenses'[Expense Per Employee]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Date'[Date], 1, 'CC'[Department], 1)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	[IsGrandTotalRowTotal] DESC, 'Date'[Date], 'CC'[Department]

You can simplify it because you don't need the totals

// DAX Query
DEFINE
	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			'Date'[Date],
			'CC'[Department],
			"SumAmount", CALCULATE(SUM('Expenses'[Amount])),
			"DistinctCountName", CALCULATE(DISTINCTCOUNT('Employees'[Name])),
			"Expense_Per_Employee", 'Expenses'[Expense Per Employee]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(501, __DS0Core, 'Date'[Date], 1, 'CC'[Department], 1)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	'Date'[Date], 'CC'[Department]

and then you can refactor it some more in DAX Studio

So your new table would be 

New Table = SUMMARIZECOLUMNS(
			'Date'[Date],
			'CC'[Department],
			"SumAmount", CALCULATE(SUM('Expenses'[Amount])),
			"DistinctCountName", CALCULATE(DISTINCTCOUNT('Employees'[Name])),
			"Expense_Per_Employee", 'Expenses'[Expense Per Employee]
		)

see attached.