Income Statement Sum by Category

[Teletubbi]

Hi,

I have a table with an account scheme like an income statement (see left table on the picture). I can create the values for each account for each month but not for the sum of those accounts in the same category. Furthermore I cannot implement calculated measures in the left table (like Total 1+ Total 2 + Total 3 = Total 4). On the right picture I calculated the totals which I want to implement in the table on the left. Do you have any ideas?

link to picture: 

For the values for each account I used: Balance = sumx(Testdaten;Testdaten[Summe])

For the sum by category (account) I used: SumbyCategory = CALCULATE(SUM(Testdaten[Summe]);ALLEXCEPT(Testschema;Testschema[Category]))

I also tried to use switch...but that doesn't show any data in the totals. Maybe I did something wrong there:

Switch =
IF (
COUNTROWS ( VALUES ( Testschema[Level]) ) = 1;
SWITCH (
VALUES ( 'Testschema'[Account] );
"Income 1"; [Balance];
"Income 2"; [Balance];
"Income 3"; [Balance];
"Income 4"; [Balance];
"Total T+T2+T3"; [Total4];

BLANK ()
);
100
)

I use a Date Table, a datatable and a table for the account scheme. Data looks like this:
Link to picture: https://ibb.co/3SK1Bnq

[AnthonyTilley]

I did this using a Grouping table along with a case statment to swap between the calulation types
-Table name = Groups
ORDER
Header
CalcType
Group

Display Details
1
T1
1
1
1
2
T1 Total
2
1
0
3
T2
1
2
1
4
T2 Total
2
2
0
5
T3
1
3
1
6
T3 Total
2
3
0
7
T4 Toal
3
4
0
 
I then Join this to my income amounts by adding a Group colunm (T1,T2,T3)
Table name = dataset
Acct
Header
INCOME 1
T1
INCOME 2
T1
INCOME 3
T1
INCOME 4
T1
INCOME 5
T1
INCOME 6
T1
INCOME 7
T2
INCOME 8
T2
INCOME 9
T3
INCOME 10
T3
 
I then Create a set of calculation measures to hold to diffrent values 
 
--this will get the min calc type for each header to be used later to switch based on calc type and Header details

HeaderCalcType = min(Groups[CalcType])


Header Detail = max(Groups[details])


Is Subheader Filtered = ISFILTERED('dataset'[Acct])
 
 
-- get the sum of your values
Fact_AMT = SUM(dataset[AMT]) 
 
-the running total will get the total of all values where the order is less than its self but where the header type is the same

 

Running AMT = If(
HASONEFILTER(Groups[Header])
,
CALCULATE(
[fact_AMT]
,
ALL(Groups)
,
Groups[ORDER] < VALUES(Groups[ORDER])
,
Groups[Group] >= VALUES(Groups[Group])
)
,
blank()
)
 
--total AMT gets the total of all values
 
Total_AMT = 
If(
HASONEFILTER(Groups[Header])
,
CALCULATE(
[fact_AMT]
,
ALL(Groups)
,
Groups[ORDER] < VALUES(Groups[ORDER])
)
,
blank()
)
 
 
i then create a Switch statment to swap between the diffrent calc types 
 


Combined Report AMT = SWITCH(
[HeaderCalcType]
,
BLANK(), BLANK()
,
1, [fact_AMT]
,
2, [Running AMT]
,
3,[Total_AMT]
,BLANK()
)
 
 
And Finally i create the income statment amt that will mean that the group total headers do not expand 
 

Income Statement AMT = (If(
OR(
AND([Header Detail]=0, [Is Subheader Filtered]),
AND([Header Detail]=1, [Is Subheader Filtered] = FALSE())
)
,
BLANK()
,
[Combined Report AMT]
))
 
Fianl Result Create a table with the following layout
 
Rows
Groups[header]
Dataset[Acct]
 
Columns
dataset[date]
 
Values
Income Statment AMT
 

HEADER
JAN
FEB
TOTAL
T1
 
 
 
INCOME 1
200
300
500
INCOME 2
200
300
500
INCOME 3
200
300
500
INCOME 4
200
300
500
INCOME 5
200
300
500
INCOME 6
200
300
500
T1 TOTAL
1200
1800
3000
T2
 
 
 
INCOME 7
200
300
500
INCOME 8
200
300
500
T2 Total
400
600
1000
T3
 
 
 
INCOME 9
200
300
500
INCOME 10
200
300
500
T3 Total
400
600
1000
T4 Total
2000
3000
5000
 
 
This can look like overkill however it is usfull later on.
 
in my version i have 30 diffrent heders and around 7 difrent calctypes
For Example Gross Profit% below finds the some of all those up to header gross profit and finds all those in header total sales and devides them to get a gross profit, i then add in a calc type 4 and in the combined amt add another level for 4 = gross Profit.
 

Gross Profit % = (DIVIDE((If(
HASONEFILTER(GROUPS[Header])
,
CALCULATE(
[AMT with Report Sign]
,
ALL(GROUPS)
,
OR(GROUPS[Order] = 15,DimHeaders[Order] = 17)
,
GROUPS[MA_TYPE_NO] = 2
)
,
blank()
)),(If(
HASONEFILTER(GROUPS[Header])
,
CALCULATE(
[AMT with Report Sign]
,
ALL(GROUPS)
,
GROUPS[Order] = 13
,
GROUPS[MA_TYPE_NO] = 2
)
,
blank()
)),BLANK())+1)*100
 
 
with this you can add in any number of diffrent calculations going down your income statment.

[v-piga-msft]

Hi @Teletubbi ,

I'm afraid something went wrong. I cannot see the image you uploaded.

If it is convenient, please share your data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry