Group by not working

[#1]

I am using group by to create a P&L but it is not working as I would expect.

See attached example

[#2 · Accepted solution]

I don't understand what is the result you expect...

If you want a table like this:

Try the following :

Calculations:
LOAD [Type],
Sum(Amount) as [Gross Margin]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' GROUP BY Type;


JOIN (Calculations) LOAD Type,
Sum(Amount) as [Profit before Tax]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' or [Level] = 'Overheads' GROUP BY Type;


join(Calculations) LOAD [Type],
Sum(Amount) as [Profit After Tax]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' or [Level] = 'Overheads' or [Level] = 'Tax' GROUP BY Type;

If you want the following :

Try :

JOIN(FinancialResultsTable) LOAD
'Gross Margin' AS [Level],
[Type],
Sum(Amount) as [Amount]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' GROUP BY Type;

JOIN(FinancialResultsTable) LOAD
'Profit before Tax' AS [Level],
[Type],
Sum(Amount) as [Amount]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' or [Level] = 'Overheads' GROUP BY Type;

JOIN(FinancialResultsTable) LOAD
'Profit After Tax' AS [Level],
[Type],
Sum(Amount) as [Amount]
Resident FinancialResultsTable
WHERE [Level] = 'Sales' or [Level] = 'Cost of Sales' or [Level] = 'Overheads' or [Level] = 'Tax' GROUP BY Type;

[#3]

The attached example is what I was trying to do.

Thanks for your help