Need help in my DAX measure query

[Khushboo19]

I have a power BI template (shown in image below)

In my power BI report, I am trying to update the values for each row in column B. I have the DAX calculations for rows which are not headings like row 3 to 8 and so on, but due to this, my other rows(like row 1 and 2) are getting removed from the power BI report: 

In the above DAX query, I tried using CurrentItem as the last switch stmt but it is throwing the error: switch does not support values of type true/false with values of type number. consider using value or format func to convert one of the values

@daxcommunity @measures 

[DataNinja777]

Hi Khushboo19,

Instead of trying to address financial statements aggrgation issue with creating a complex dax measure, I address this isssue by creating proper mapping table (dimension table) which should be linked in the data model with fact tables (like trial balance and transations tables).  Sorting out GL account mapping to each financial statements aggregation disclosure lines is the first thing I would do in automaing financial reporting. Once this is done, it would be just a matter of aggregating the trial balance numbers which should add up to zero to be mapped to each financial statements disclosure line.  

[Khushboo19]

Thanks for looking into it Sakiko. 

I think, there is some gap, below is how my data looks like: 

The challenge is not incaluclating the value for the category or sub category. The challenge that I am facing is in the switch query which is hiding up the headings in my template table. If you see below image, the headings "Assets" and "Current Assets" are visible, but when I import the data using prev shown switch query they are gone as I don't have any logic written to populate that:

Hope, I was able to explain my question.

[DataNinja777]

Hi Khushboo19

Did you get that fact table from an excel file?  Normally, I download such information from ERP (like SAP's trial balance) at the GL account granularity level to prepare the financial statements, but your fact table is already aggregated at the subcategory granularity level.  That's OK, I guess, but I think it is better that you do not repeat in your fact table the aggregation mapping like category and Balance Sheet Type.  Instead, I would separate the fact table (the BS values and Year and Subcategory fields) and dimension table (Subcategory, Category and Balance Sheet Type fields), and create a relationship using the subcategory field (if this is the smallest granurality key in your data model).  After you created the data model, I guess there are multiple ways to accomplish your task of subtotalling of the BS line items with proper balance sheet sort order (i.e., most liquid asset types at the top to least liquid liability types, then share capital and retained earnings at the bottom. If you'd like, please share with me the sample file, and I can try to sort out the data model.

[Khushboo19]

The file is from one of the Udemy course that I am doing and it is teaching on the ease of financial reporting. Along with data model, if you could givea shot at the DAX on your side to see if it is working for you https://docs.google.com/spreadsheets/d/1X5HMbg-IeQYfMhCXCGggkepEdMCj6Lby/edit?usp=drive_link&ouid=10... 

[DataNinja777]

Hi @Khushboo19 

Thanks for sharing the excercise. I had a look at the material, and noted that the balance sheet does not balance because Total assets ≠ Total liabilities and equity in the raw data table:

Also, Power BI also showed the same output.  

In order to make the data model simple, I've cleansed the messy BS Template table and produced the mapping dimension table which will be linked with the fact table with the BS numbers.   The part which was tricky for this excercise was the fact that there were 3 "Other" items (one in assets, one in liablities, and one in equity), and "Deferred income tax" (one in assets and one in liabilities), so I had to make them unique by concatenating with other columns to distinguish from one another.   After these clensing activities are done, I could create a relationship between BS Template dimension table and Balance Sheet Data fact table. Before creating the relationship, I unpivoted Balance Sheet Data fact table so that I only needed to create one aggregation measure for the balance sheet and not 5 measures for each of the years between 2014 and 2018.  

Hi @Khushboo19 

Thanks for sharing the excercise. I had a look at the material, and noted that the balance sheet does not balance because Total assets ≠ Total liabilities and equity in the raw data table:

Also, Power BI also showed the same output.  

In order to make the data model simple, I've cleansed the messy BS Template table and produced the mapping dimension table which will be linked with the fact table with the BS numbers.   The part which was tricky for this excercise was the fact that there were 3 "Other" items (one in assets, one in liablities, and one in equity), and "Deferred income tax" (one in assets and one in liabilities), so I had to make them unique by concatenating with other columns to distinguish from one another.   After these clensing activities are done, I could create a relationship between BS Template dimension table and Balance Sheet Data fact table. 

Before creating the relationship, I unpivoted Balance Sheet Data fact table so that I only needed to create one aggregation measure for the balance sheet and not 5 measures for each of the years between 2014 and 2018.  

The data model for this task is shown below:

Although all the numbers in the fact tables were shown as positive sign, the assets have to be aggregated using positive sign, while the liabilities and equity have to be aggregated using negative sign, so I've written the dax measure below:

The order of liquidity on the BS (the most liquid assets at the top to the least liquid liabilities at the bottom) was sorted out in the data view using the sort by column icon as shown below.  

If you'd like to have the pbix file for this excercise which I've done, please let me know.  

[Khushboo19 · Accepted solution]

Thanks for such a hard work Sakiko, really appreciate it. 
But, today I got to know where the issue was in the DAX. in my dax query if I format "BS Values " to text field then it is showing up result for all the rows (even for the headings which don't have any calculated value in the o/p). As you can see assets and current assets are visibile now