Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there,
I am currently preparing a Balance Sheet based on some dummy data (data is clean).
Here are some screenshots for you to follow where I am currently at in the process:
1. This is the Balance Sheet (Matrix Design):
The reason why the data has the same values is because I created a measure called "BS Values" with the following formula:
2. This is the SWITCH/ TRUE formula I used based on the first formula:
Formula written:
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", [Fixed Assets],
CurrentItem = "Total Other Assets", [Other Assets],
CurrentItem = "Total Assets" = [Total Assets],
CurrentItem = "Total current liabilities" = [Current Liabilities],
CurrentItem = "Total long-term liabilities" = [Long-Term Liabilities],
CurrentItem = "Total owner's equity" = [Owner's Equity],
CurrentItem = "Total Liabilities and Owner's Equity", [Liabilities And Owners Equity],
CurrentItem = "Debt Ratio (Total Liabilities / Total Assets)", FORMAT(DIVIDE([Total Liabilities],[Total Assets],0),"0.00"),
CurrentItem = "Current Ratio (Current Assets / Current Liabilities)", FORMAT(DIVIDE([Current Assets],[Current Liabilities],0),"0.00"),
CurrentItem = "Working Capital (Current Assets - Current Liabilities)", FORMAT([Current Assets] - [Current Liabilities],"0"),
CurrentItem = "Assets-to-Equity Ratio (Total Assets / Owner's Equity)", FORMAT(DIVIDE([Total Assets],[Owner's Equity],0),"0.00"),
CurrentItem = "Debt-to-Equity Ratio (Total Liabilities / Owner's Equity)",FORMAT(DIVIDE([Total Liabilities],[Owner's Equity],0),"0.00"),
CALCULATE([BS Values], FILTER('Balance Sheet Data','Balance Sheet Data'[Sub Category]= CurrentItem)))
3. HOWEVER, when I input the following formula into my matrix design, I am getting the following error:
I'm not really sure where I'm going wrong. In the Transform data i.e. PowerQuery, The Year column in the Dates table and the Balance Sheet [Dates] table is both set at ABC.
Please let me know where I'm going wrong. I need to be able to figure this out before I can move on to the Cash Flow Statement!
Solved! Go to Solution.
Hi @burhanml,
I fix the typo and remove these FORMAT function usages from your expressions, you can try to use the following measure formula if it work on your side:
B/S Values =
VAR CurrentItem =
SELECTEDVALUE ( 'Balance Sheet Template'[Balance Sheet Normalized] )
RETURN
SWITCH (
CurrentItem,
"Total current assets", [Current Assets],
"Total fixed assets", [Fixed Assets],
"Total Other Assets", [Other Assets],
"Total Assets", [Total Assets],
"Total current liabilities", [Current Liabilities],
"Total long-term liabilities", [Long-Term Liabilities],
"Total owner's equity", [Owner's Equity],
"Total Liabilities and Owner's Equity", [Liabilities And Owners Equity],
"Debt Ratio (Total Liabilities / Total Assets)", DIVIDE ( [Total Liabilities], [Total Assets], 0 ),
"Current Ratio (Current Assets / Current Liabilities)", DIVIDE ( [Current Assets], [Current Liabilities], 0 ),
"Working Capital (Current Assets - Current Liabilities)", [Current Assets] - [Current Liabilities],
"Assets-to-Equity Ratio (Total Assets / Owner's Equity)", DIVIDE ( [Total Assets], [Owner's Equity], 0 ),
"Debt-to-Equity Ratio (Total Liabilities / Owner's Equity)", DIVIDE ( [Total Liabilities], [Owner's Equity], 0 ),
CALCULATE (
[BS Values],
FILTER (
'Balance Sheet Data',
'Balance Sheet Data'[Sub Category] = CurrentItem
)
)
)
Notice: FORMAT function will convert the result to text type, current power bi not support one calculate field return multiple data types.
FORMAT function (DAX) - DAX | Microsoft Learn
I'd like to suggest you to do these formatting operations on the field string format.
Use custom format strings in Power BI Desktop - Power BI | Microsoft Learn
Regards,
Xiaoxin Sheng
Your dax query is right only remove format function wherever you have written in query
Hi @burhanml,
I fix the typo and remove these FORMAT function usages from your expressions, you can try to use the following measure formula if it work on your side:
B/S Values =
VAR CurrentItem =
SELECTEDVALUE ( 'Balance Sheet Template'[Balance Sheet Normalized] )
RETURN
SWITCH (
CurrentItem,
"Total current assets", [Current Assets],
"Total fixed assets", [Fixed Assets],
"Total Other Assets", [Other Assets],
"Total Assets", [Total Assets],
"Total current liabilities", [Current Liabilities],
"Total long-term liabilities", [Long-Term Liabilities],
"Total owner's equity", [Owner's Equity],
"Total Liabilities and Owner's Equity", [Liabilities And Owners Equity],
"Debt Ratio (Total Liabilities / Total Assets)", DIVIDE ( [Total Liabilities], [Total Assets], 0 ),
"Current Ratio (Current Assets / Current Liabilities)", DIVIDE ( [Current Assets], [Current Liabilities], 0 ),
"Working Capital (Current Assets - Current Liabilities)", [Current Assets] - [Current Liabilities],
"Assets-to-Equity Ratio (Total Assets / Owner's Equity)", DIVIDE ( [Total Assets], [Owner's Equity], 0 ),
"Debt-to-Equity Ratio (Total Liabilities / Owner's Equity)", DIVIDE ( [Total Liabilities], [Owner's Equity], 0 ),
CALCULATE (
[BS Values],
FILTER (
'Balance Sheet Data',
'Balance Sheet Data'[Sub Category] = CurrentItem
)
)
)
Notice: FORMAT function will convert the result to text type, current power bi not support one calculate field return multiple data types.
FORMAT function (DAX) - DAX | Microsoft Learn
I'd like to suggest you to do these formatting operations on the field string format.
Use custom format strings in Power BI Desktop - Power BI | Microsoft Learn
Regards,
Xiaoxin Sheng
@burhanml Looks like lines 9, 10, 11, and 12 have an = where a , is needed.
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |