problem in DAX calculation total and subtotals

[Aslam_mh]

want to output like attached photo but shows error when i calculate grand total using 2 DAX funtions

for display im using balancesheeet table templet

Row Index	Balance Sheet Items	Summary Items	Balance Sheet Normalized	Summary Index
1	Assets		Assets
2	Current Assets		Current Assets
3	Cash		Cash
4	Accounts receivable		Accounts receivable
5	Inventory		Inventory
6	Prepaid expenses		Prepaid expenses
7	Short-term investments		Short-term investments
8	Total current assets	Total current assets	Total current assets	1
9	Fixed (Long-Term) Assets		Fixed (Long-Term) Assets
10	Long-term investments		Long-term investments
11	Property, plant, and equipment		Property, plant, and equipment
12	(Less accumulated depreciation)		(Less accumulated depreciation)
13	Intangible assets		Intangible assets
14	Total fixed assets	Total fixed assets	Total fixed assets	2
15	Other Assets		Other Assets
16	Deferred income tax		Deferred income tax
17	Other		Other
18	Total Other Assets	Total Other Assets	Total Other Assets	3
19
20	Total Assets	Total Assets	Total Assets	4

for data balance sheet data

Balance Sheet Type	Category	Sub Category	Year	Values
Assets	Current Assets	Cash	2019	11874
Assets	Current Assets	Cash	2020	11875
Assets	Current Assets	Cash	2021	11876
Assets	Current Assets	Cash	2022	11877
Assets	Current Assets	Cash	2023	11879
Assets	Current Assets	Accounts receivable	2019	4215
Assets	Current Assets	Accounts receivable	2020	4216
Assets	Current Assets	Accounts receivable	2021	4217
Assets	Current Assets	Accounts receivable	2022	4218
Assets	Current Assets	Accounts receivable	2023	4220
Assets	Current Assets	Inventory	2019	2145
Assets	Current Assets	Inventory	2020	2146
Assets	Current Assets	Inventory	2021	2147
Assets	Current Assets	Inventory	2022	2148
Assets	Current Assets	Inventory	2023	2150
Assets	Current Assets	Prepaid expenses	2019	354
Assets	Current Assets	Prepaid expenses	2020	355
Assets	Current Assets	Prepaid expenses	2021	356
Assets	Current Assets	Prepaid expenses	2022	357
Assets	Current Assets	Prepaid expenses	2023	359
Assets	Current Assets	Short-term investments	2019	254
Assets	Current Assets	Short-term investments	2020	255
Assets	Current Assets	Short-term investments	2021	256
Assets	Current Assets	Short-term investments	2022	257
Assets	Current Assets	Short-term investments	2023	259
Assets	Fixed (Long-Term) Assets	Long-term investments	2019	1208
Assets	Fixed (Long-Term) Assets	Long-term investments	2020	1209
Assets	Fixed (Long-Term) Assets	Long-term investments	2021	1210
Assets	Fixed (Long-Term) Assets	Long-term investments	2022	1211
Assets	Fixed (Long-Term) Assets	Long-term investments	2023	1213
Assets	Fixed (Long-Term) Assets	Property, plant, and equipment	2019	15340
Assets	Fixed (Long-Term) Assets	Property, plant, and equipment	2020	15341
Assets	Fixed (Long-Term) Assets	Property, plant, and equipment	2021	15342
Assets	Fixed (Long-Term) Assets	Property, plant, and equipment	2022	15343
Assets	Fixed (Long-Term) Assets	Property, plant, and equipment	2023	15345
Assets	Fixed (Long-Term) Assets	(Less accumulated depreciation)	2019	-2200
Assets	Fixed (Long-Term) Assets	(Less accumulated depreciation)	2020	-2199
Assets	Fixed (Long-Term) Assets	(Less accumulated depreciation)	2021	-2198
Assets	Fixed (Long-Term) Assets	(Less accumulated depreciation)	2022	-2197
Assets	Fixed (Long-Term) Assets	(Less accumulated depreciation)	2023	-2195
Assets	Fixed (Long-Term) Assets	Intangible assets	2019	2215
Assets	Fixed (Long-Term) Assets	Intangible assets	2020	2216
Assets	Fixed (Long-Term) Assets	Intangible assets	2021	2217
Assets	Fixed (Long-Term) Assets	Intangible assets	2022	2218
Assets	Fixed (Long-Term) Assets	Intangible assets	2023	2220
Assets	Other Assets	Deferred income tax	2019	134
Assets	Other Assets	Deferred income tax	2020	135
Assets	Other Assets	Deferred income tax	2021	136
Assets	Other Assets	Deferred income tax	2022	137
Assets	Other Assets	Deferred income tax	2023	139
Assets	Other Assets	Other	2019	324
Assets	Other Assets	Other	2020	325
Assets	Other Assets	Other	2021	326
Assets	Other Assets	Other	2022	327
Assets	Other Assets	Other	2023	329
Liabilities	Current Liabilities	Accounts payable	2019	8060
Liabilities	Current Liabilities	Accounts payable	2020	8061
Liabilities	Current Liabilities	Accounts payable	2021	8062
Liabilities	Current Liabilities	Accounts payable	2022	8063
Liabilities	Current Liabilities	Accounts payable	2023	8065
Liabilities	Current Liabilities	Short-term loans	2019	200
Liabilities	Current Liabilities	Short-term loans	2020	201
Liabilities	Current Liabilities	Short-term loans	2021	202
Liabilities	Current Liabilities	Short-term loans	2022	203
Liabilities	Current Liabilities	Short-term loans	2023	205
Liabilities	Current Liabilities	Income taxes payable	2019	3145
Liabilities	Current Liabilities	Income taxes payable	2020	3146
Liabilities	Current Liabilities	Income taxes payable	2021	3147
Liabilities	Current Liabilities	Income taxes payable	2022	3148
Liabilities	Current Liabilities	Income taxes payable	2023	3150

DAX funtions

B/S Values =

VAR CurrentItem = SELECTEDVALUE( 'Balance Sheet Template'[Balance Sheet Normalized] )

RETURN

SWITCH( TRUE(),

    CurrentItem = "Total current assets", [Current Assets],

    CurrentItem = "Total fixed assets", [FixedAssets],

    CurrentItem = "Total Other assets", [Other Assets],

    CurrentItem = "Total Assets", [Total Assets], --------------------- ERROR

    CurrentItem = "Total current liabilities", [Current Liabilities],

    CurrentItem = "Total long-term liabilities", [Long-Term Liabilities],

    CurrentItem = "Total owner's equity", [Owners Equity],

    CurrentItem = "Total Liabilities and Owner's Equity", [Liabilities and Owners Equity], ---------- ERROR  [Current Liabilities] +  [Long-Term Liabilities]  unable to fetch data-------

        CALCULATE( [BS Values], FILTER( 'Balance Sheet Data', 'Balance Sheet Data'[Sub Category] = CurrentItem )  ))

BS Values =

CALCULATE( SUM( 'Balance Sheet Data'[Values] ),

    TREATAS( VALUES( Dates[Year] ), 'Balance Sheet Data'[Year] ) )

Current Assets =

SUMX(

    FILTER(

        'Balance Sheet Data',

        'Balance Sheet Data'[Category] = "Current Assets" &&

        TREATAS( VALUES( Dates[Year] ), 'Balance Sheet Data'[Year] )

    ),

    'Balance Sheet Data'[Values]

)

Fixed Assets = SUMX( FILTER( 'Balance Sheet Data', 'Balance Sheet Data'[Category] = "Fixed (Long-Term) Assets" ), [BS Values] )

total asset can be get but some other calculation we can't use 

Total Assets =

SUMX (

    FILTER (

        'Balance Sheet Data',

        'Balance Sheet Data'[Category] IN {"Current Assets", "Fixed (Long-Term) Assets", "Other Assets"}

    ),

    'Balance Sheet Data'[Values]

)

DAX like ------- [total assets]/ [current asset]


solotion need to diplay result like shown in photo , please help

[danextian]

It would be easier for us to figure out what's causing the error if you shared a sanitized copy of your pbix for several reasons:

• we will have to recreate each measure and we don't even know what exact formula you used
• our calculations might not return an error

Also, your B/S Values references [Long-Term Liabilities] but you have ommitted that as well as the equity section.

[Aslam_mh]

finance pbix 

[DataNinja777]

Hi @Aslam_mh ,

I recommend addressing the balance sheet subtotal using a chart of accounts mapping table instead of incorporating it directly into your fact table. You can create a hierarchical mapping table, where the smallest unit represents GL accounts, and the largest aggregates into financial statement lines, such as total assets or total liabilities and shareholders' equity.

Additionally, I suggest displaying assets as positive numbers and liabilities and equity as negative numbers. This approach allows you to verify that the balance sheet consistently balances (adds up to zero).

Mapping table like below will be used as a dimention table and then relationship will be created with your fact table.

With this approach, there’s no need to create a complex DAX formula. A simple SUM formula (or a cumulative SUM formula for transactional data) will be sufficient to achieve the desired result.

Row Index	Hierarchy Level 3	Summary Index	Hierarchy Level 2	Hierarchy Level 1
1	Cash	1	Current Assets	Assets
2	Accounts receivable	1	Current Assets	Assets
3	Inventory	1	Current Assets	Assets
4	Prepaid expenses	1	Current Assets	Assets
5	Short-term investments	1	Current Assets	Assets
6	Long-term investments	2	Fixed (Long-Term) Assets	Assets
7	Property, plant, and equipment	2	Fixed (Long-Term) Assets	Assets
8	(Less accumulated depreciation)	2	Fixed (Long-Term) Assets	Assets
9	Intangible assets	2	Fixed (Long-Term) Assets	Assets
10	Deferred income tax	3	Other Assets	Assets
11	Other	3	Other Assets	Assets

Best regards,