Balance Sheet in Matrix format with time period drilldown?

[mrothschild]

Excel file is here with two tabs, data, and Desired Output: https://drive.google.com/open?id=1s7lSQ15dhHeIJryeQ2Op6h2-lzLqPvo6

Data is also posted below.  The problem I'm running into on a few different fronts.  The MAXX measure I use is effective at the lowest level of the hierarchy: 

MAXX Measure = 
    CALCULATE(
        MAXX(
            GROUPBY('Management Company Balance Sheet','Management Company Balance Sheet'[Month],'Management Company Balance Sheet'[Value - Items]),
            'Management Company Balance Sheet'[Value - Items])
        
    )

but then doesn't subtotal.  SUMX was problematic, which is why I switched to MAXX.  I'm not that familiar with SWITCH(TRUE(). . .) but think that could be the solution.

Category	Group	Sub-Category	Item Description	Balance Sheet Item	Calendar Quarter	Calendar Year	Table Value	SUM Measure	MAXX Measure
Assets	Current Assets	Cash and Cash Equivalents	Item	Bank2	Q3	2019	$585,559	$585,559	$585,559
Assets	Current Assets	Cash and Cash Equivalents	Item	Bank1	Q1	2019	$1,220,050	$1,220,050	$435,017
Assets	Current Assets	Cash and Cash Equivalents	Item	Bank1	Q2	2019	$1,305,050	$1,305,050	$435,017
Assets	Current Assets	Cash and Cash Equivalents	Item	Bank1	Q3	2019	$435,017	$435,017	$435,017
Equity	Shareholders' Equity	Net Income	Item	Net Income - Balance Sheet	Q3	2019	$427,060	$427,060	$427,060
Equity	Shareholders' Equity	Owners' Equity	Item	Owner1 - Equity	Q1	2019	$1,050,000	$1,050,000	$350,000
Equity	Shareholders' Equity	Owners' Equity	Item	Owner1 - Equity	Q2	2019	$1,050,000	$1,050,000	$350,000
Equity	Shareholders' Equity	Owners' Equity	Item	Owner1 - Equity	Q3	2019	$350,000	$350,000	$350,000
Assets	Current Assets	Cash and Cash Equivalents	Item	Bank1	Q4	2018	$850,050	$850,050	$300,017
Equity	Shareholders' Equity	Owners' Equity	Item	Owner1 - Equity	Q4	2018	$850,000	$850,000	$300,000
Assets	Current Assets	Cash and Cash Equivalents	Item	Bank1	Q2	2018	$450,047	$450,047	$250,017
Assets	Current Assets	Cash and Cash Equivalents	Item	Bank1	Q3	2018	$750,050	$750,050	$250,017
Equity	Shareholders' Equity	Owners' Equity	Item	Owner1 - Equity	Q2	2018	$450,000	$450,000	$250,000
Equity	Shareholders' Equity	Owners' Equity	Item	Owner1 - Equity	Q3	2018	$750,000	$750,000	$250,000
Equity	Shareholders' Equity	Net Income	Item	Net Income - Balance Sheet	Q2	2019	($21,456)	($21,456)	$51,042
Assets	Current Assets	Cash and Cash Equivalents	Item	Bank2	Q2	2019	$150,003	$150,003	$50,001
Equity	Shareholders' Equity	Net Income	Item	Net Income - Balance Sheet	Q1	2019	$56,241	$56,241	$47,071
Assets	Current Assets	Cash and Cash Equivalents	Item	Bank2	Q1	2019	$0	$0	$0
Assets	Current Assets	Cash and Cash Equivalents	Item	Bank2	Q2	2018	$0	$0	$0
Assets	Current Assets	Cash and Cash Equivalents	Item	Bank2	Q3	2018	$0	$0	$0
Assets	Current Assets	Cash and Cash Equivalents	Item	Bank2	Q4	2018	$0	$0	$0
Equity	Shareholders' Equity	Owners' Equity	Item	Owner2 - Equity	Q1	2019	$0	$0	$0
Equity	Shareholders' Equity	Owners' Equity	Item	Owner2 - Equity	Q2	2018	$0	$0	$0
Equity	Shareholders' Equity	Owners' Equity	Item	Owner2 - Equity	Q2	2019	$0	$0	$0
Equity	Shareholders' Equity	Owners' Equity	Item	Owner2 - Equity	Q3	2018	$0	$0	$0
Equity	Shareholders' Equity	Owners' Equity	Item	Owner2 - Equity	Q3	2019	$0	$0	$0
Equity	Shareholders' Equity	Owners' Equity	Item	Owner2 - Equity	Q4	2018	$0	$0	$0
Equity	Shareholders' Equity	Owners' Equity	Item	Retained Earnings	Q2	2018	$0	$0	$0
Equity	Shareholders' Equity	Owners' Equity	Item	Retained Earnings	Q3	2018	$0	$0	$0
Equity	Shareholders' Equity	Owners' Equity	Item	Retained Earnings	Q4	2018	$0	$0	$0
Category total		Summary	Total	Total Assets	Q1	2019	$1,220,050
Category total		Summary	Total	Total Assets	Q2	2018	$450,047
Category total		Summary	Total	Total Assets	Q2	2019	$1,455,053
Category total		Summary	Total	Total Assets	Q3	2018	$750,050
Category total		Summary	Total	Total Assets	Q3	2019	$1,020,575
Category total		Summary	Total	Total Assets	Q4	2018	$850,050
Category total		Summary	Total	Total Liabilities and Equity	Q1	2019	$534,346
Category total		Summary	Total	Total Liabilities and Equity	Q2	2018	$324,746
Category total		Summary	Total	Total Liabilities and Equity	Q2	2019	$456,650
Category total		Summary	Total	Total Liabilities and Equity	Q3	2018	$487,005
Category total		Summary	Total	Total Liabilities and Equity	Q3	2019	$586,428
Category total		Summary	Total	Total Liabilities and Equity	Q4	2018	$345,334
Group total		Summary	Sub-Total	Total Current Assets	Q1	2019	$1,220,050
Group total		Summary	Sub-Total	Total Current Assets	Q2	2018	$450,047
Group total		Summary	Sub-Total	Total Current Assets	Q2	2019	$1,455,053
Group total		Summary	Sub-Total	Total Current Assets	Q3	2018	$750,050
Group total		Summary	Sub-Total	Total Current Assets	Q3	2019	$1,020,575
Group total		Summary	Sub-Total	Total Current Assets	Q4	2018	$850,050
Group total		Summary	Sub-Total	Total Equity	Q1	2019	$534,346
Group total		Summary	Sub-Total	Total Equity	Q2	2018	$324,746
Group total		Summary	Sub-Total	Total Equity	Q2	2019	$456,650
Group total		Summary	Sub-Total	Total Equity	Q3	2018	$487,005
Group total		Summary	Sub-Total	Total Equity	Q3	2019	$586,428
Group total		Summary	Sub-Total	Total Equity	Q4	2018	$345,334
Sub-category total		Summary	Sub-Sub-Total	Total Bank Accounts	Q1	2019	$1,220,050
Sub-category total		Summary	Sub-Sub-Total	Total Bank Accounts	Q2	2018	$450,047
Sub-category total		Summary	Sub-Sub-Total	Total Bank Accounts	Q2	2019	$1,455,053
Sub-category total		Summary	Sub-Sub-Total	Total Bank Accounts	Q3	2018	$750,050
Sub-category total		Summary	Sub-Sub-Total	Total Bank Accounts	Q3	2019	$1,020,575
Sub-category total		Summary	Sub-Sub-Total	Total Bank Accounts	Q4	2018	$850,050
Equity	Shareholders' Equity	Net Income	Item	Net Income - Balance Sheet	Q2	2018	($125,254)	($125,254)	($31,494)
Equity	Shareholders' Equity	Net Income	Item	Net Income - Balance Sheet	Q3	2018	($262,995)	($262,995)	($60,230)
Equity	Shareholders' Equity	Net Income	Item	Net Income - Balance Sheet	Q4	2018	($504,666)	($504,666)	($147,541)
Equity	Shareholders' Equity	Owners' Equity	Item	Retained Earnings	Q1	2019	($571,895)	($571,895)	($190,632)
Equity	Shareholders' Equity	Owners' Equity	Item	Retained Earnings	Q2	2019	($571,895)	($571,895)	($190,632)
Equity	Shareholders' Equity	Owners' Equity	Item	Retained Earnings	Q3	2019	($190,632)	($190,632)	($190,632)

Desired output when time period is drilled down to Quarters:

Balance Sheet Item	2019-Q1	2019-Q2	2019-Q3
Bank1	435,017	435,017	435,017
Bank2	-	50,001	585,559
Cash & Cash Equivalents	435,017	485,018	1,020,575
Current Assets	435,017	485,018	1,020,575
Total Assets	435,017	485,018	1,020,575
Liabilities	-	-	-
Total Liabilities	-	-	-
Owner1 - Equity	350,000	350,000	350,000
Owner2 - Equity	-	-	-
Owner's Equity	350,000	350,000	350,000
Retained Earnings	(190,632)	(190,632)	(190,632)
Net Income - Balance Sheet	28,147	(74,268)	427,060
Total Equity	187,515	85,100	586,428

Desired output when drilled up to Year:

Balance Sheet Item	2019
Bank1	435,017
Bank2	585,559
Cash & Cash Equivalents	1,020,575
Current Assets	1,020,575
Total Assets	1,020,575
Liabilities	-
Total Liabilities	-
Owner1 - Equity	350,000
Owner2 - Equity	-
Owner's Equity	350,000
Retained Earnings	(190,632)
Net Income - Balance Sheet	427,060
Total Equity	586,428

[v-xuding-msft]

Hi @mrothschild ,

I don't get you. I find that some values of Balance Sheet Items in the expected output table is not in the original table, like "Cash & Cash Equivalents" and "Owner's Equity". They are in the "Sub-category" column. And in the Matrix, it will sum the values based on the values in the "Row" field automatically. So you want to re-create the vaules? Or you want to show the values for 2019? 

I create a sample to calculate total values in 2019.

Measure = CALCULATE(SUM('Management Company Balance Sheet'[Table Value]),FILTER('Management Company Balance Sheet','Management Company Balance Sheet'[Calendar Year] = 2019))

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mrothschild]

Hi @v-xuding-msft 

These are "stock" numbers, as opposed to "flow" numbers.  They are a snapshot of what's in the account(s) at a given time, so they do not sum across columns.  

So to simplify, imagine you had $100 in one bank and $50 in another bank, and you owned $100 of art in Q3.  In Q4, you received $75, and you put it in all three accounts equally.  So you're totals for Q4 are $125, $75, and $100, respectively - note that Total Assets shown below cannot be GRAND TOTAL, because you'll have liabilities/equity also, so say, $25 in outstanding credit card balance

The desired format in Quarterly drill down would be the table below.  For a yearly drill up, Q4 = last period of the year, so it would just show those totals, with the column header showing 2019.

But, I also want to be able to drill up/down by rows, so that I show only Total Assets, Total Liabilities, and Total Equity.  But I'm guessing in your matrix, if one were to drill up by rows that would result in double-counting of total assets, which is probably the most significant problem I'm facing.  

TIME	Q3	Q4
Assets Header
Bank1	$100	$125
Bank2	$50	$75
Sub-total Cash	$150	$200
Art	$75	$100
Total Assets	$225	$300
Liabilities Header
Credit Card balances	$25	$25
Total Liabilities	$25	$25
Equity Header
Spouse1 Equity	$100	$137.5
Spouse2 Equity	$100	$137.5
Total Spouse Equity	$200	$275
Total Equity	$200	$275
Total Liabilites and Equity	$225	$300