- Sales $100
- Cost of sales $50
- Gross Profit $50 ($100 - $50) (this is the sub-total I want to calculate)
- Variable Costs $10
- Operating Profit $40 ($50 - $10) (this is the sub-total I want to calculate)
- Fixed Costs $20
- Net Profit $20 ($40 - $20) (this is the sub-total I want to calculate)
Jagan, the pivot idea only shows a 1 total at the bottom - not sub-totals in between the data (e.g. after COS I'd like to see Gross Profit calculated as a sub-total $100 less $50 cost of sales. Only the 4 fields Sales/COS/VC/FC are in the column titled 'Group', which is my dimension.
Income Statement R Sales 100 COS 50 VC 10 FC 20 TOTAL 20
Jason, the other fields are product type, date, then actual & budget.
Your assistance is much appreciated!
You need to create your sub-total categories in the Group field and link them to your data in the sames way as the other categories are linked.
This is an example:
load * inline [
Cost of Sales, 50
'Gross Profit' as Group,
if(Group='Sales',1,-1) as Coefficient
where Group = 'Sales' or Group = 'Cost of Sales'
Load * Resident AdditionalData;
drop table AdditionalData;
Chart dimension should be Group and the formula for the amount like that:
sum(Amount * if(isnull(Coefficient),1,Coefficient))
Additional groups can also be defined and linked to the existing groups in Excel.