Forum Discussion

Lharfenes's avatar
Lharfenes
Frequent Visitor
3 years ago

Help with a calculated table

Hi, I am in need of creating a calculated table based off other tables.  Screenshot supplied above for clarity.   Thank you.
Need Help
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

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-Forum/ba-p/963216
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/1447523

Lharfenes's avatar
Lharfenes
Frequent Visitor
3 years ago

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

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        3 years ago

        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.