Issue /w Switch True

DAX DAX Calculations
1

Hello,

I am trying to apply the ideas shown in “Financial Reporting /w Power BI” to my own data and financial statement format. I am running into an issue with Switch true where it works for all of the financial statement line items EXCEPT for the subtotals created in the same manner as the as the webinar.

The first screenshot shows that the Switch True formula is getting all of the detailed line items but it is missing the subtotal. Whats confusing me is that if I put that subtotal into a card it returns what I was expecting to show up in the subtotal line.

image
image758×982 90.7 KB

I am using the following code:

Power BI Formula
Power BI Formula898×231 34.2 KB

I just don’t really understand why it shows up in a card but not in the Switch True formula. Edit - I also find that if I switch [Total Revenue] with a static number like 1,000 then the 1,000 will in fact show up.

Total Revenue Formula is as follows:

image

Actual Formula is as follows:

image

A snapshot of the data is as follows:

image
image2337×192 20.2 KB

Formatting file attached.Financial Statement Format.xlsx (33.4 KB)

Any ideas or more info required?

2

@agagnon The Total Revenue on the matrix is different from what is written inside the SWITCH, the one in the matrix has spaces/indentation.

" Total Revenue" vs “Total Revenue”

Create a calculated column to identify the length of the column that has Total Revenue keyword and then add the same number of space in your SWITCH code

1 Like
3

@AntrikshSharma Thank you for the speedy response.

I thought in the course Sam creates a “normalized” column which I thought is what I was referencing in both sections. I attached the financial statement format Excel do you still think that is the problem?

I also just tried taking out all of the spaces on my financial statement formatting file and that also did not seem to do the trick.

4

Yup my bad, I didn’t see SELECTEDVALUE was referring to a different column, sorry!

Can you share your PBI file?

5

Finance Model V1.pbix (204.5 KB)

@AntrikshSharma Of course, here it is!

Much appreciated.

6

Hello @agagnon,

Thank You for posting your query onto the Forum.

Well the problem was not with the "SWITCH( TRUE() )" function. It was actually due to the relationship that you’d created between between the models. Below is the screenshot provided for the reference -

Financial Model - 1
Financial Model - 1800×680 45 KB

Now, let’s understand why the Sub - Totals were not getting reflected but the Total of each individual line items were being shown.

In the Financial Template Table, you’ve a column with the name “GL Account Code” and under that you’d the codes of the each individual line items as per the system generated. But then you’ve added your custom codes also defining the “Sub-Totals” category. That is you’ve assigned the GL Account Codes as as provided in the screenshot below in your Financial Template Table to the following line items -

Financial Model - 2
Financial Model - 21622×121 28.8 KB

But when you refer you “Actual Table” you don’t have any such codes under the column "GL Account Code. Below is the screenshot provided for the reference -

Financial Model - 3
Financial Model - 31558×788 291 KB

So now when you’ve created “One-to-Many” relationships between your Template Data and Actual Data on the basis of “GL Account Code” it recognised only those codes which were available in both of these tables and therefore, it ignored the “GL Account Codes - 1, 2 and 3” and this is the reason why it showed you the blank under the sub-totals. Below is the screenshot provided for the reference -

Financial Model - 4
Financial Model - 4496×602 63.6 KB

And now, because of this I discarded the relationship between between the Template Data and the Actual Data. Below is the screenshot provided for the reference -

Financial Model - 5
Financial Model - 5786×664 38 KB

Now, since I’ve discarded the relationship your formula alongwith the visual will also collapse due to the wrong referencing made with the table. In the given screenshot, you’ll observe that you’ve referenced the Template table whereas it should’ve been Actual Table -

Financial Model - 6
Financial Model - 61143×244 51.5 KB

So the formula referencing itself is wrong since the beginning of your analysis. In this case, it means to say that under the Template table, Normalized Items should be equal to the Normalized Items of that table itself. And now, also due to no relationship between the Template and the Actual Table, the visualization will show the undesired result -

Financial Model - 7
Financial Model - 7554×740 87.4 KB

So now the correct formula will be as provided in the screenshot below -

Financial Model - 8
Financial Model - 8812×246 36.1 KB

And now, the visualization will show the correct “Sub-Totals” as provided in the screenshot below -

Financial Model - 9
Financial Model - 9692×727 102 KB

I’m also attaching the working of PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Please Note: To calculate the “Annual Total - Harsh”, I’ve followed the @sam.mckay’s method. Please refer the file to understand how that’s been done.

Thanks and Warm Regards,
Harsh

Finance Model V1.pbix (203.9 KB)

7

@Harsh Thank you so much! I appreciate the extremely detailed answer. I figured it had to be something other than the Switch True() formula since the visual worked if I plugged in a static number. I will review the file this morning to make sure I understand the solution!

My ultimate goal is to be able to create a template that has 3 levels of detail. I intend on doing one higher and one lower level of detail. The less detailed will break out the numbers into things like earned revenue contributed revenue, labor expenses, and non labor expenses. The more detailed would show every GL account nested withing its grouping.

I’ve already done this in another model but was unable to get the math to workout for totals. I got around this by simply taking all of my expense data and multiplying it by negative one so that my net surplus (deficit) worked. I was able to use a matrix and just add on the different levels of detail by adding rows. See screenshot below.

image

image

I am trying to utilize what I have been learning using this resource to take my existing model and make it more user friendly.

Thank you very much!

8

Hello @agagnon,

You’re Welcome. :slightly_smiling_face:

I’m glad I was able to help you.

Lastly, before I sign off from this post you can check out few links that I’ve provided below to achieve the level of formatting in the Financial Statements that you’re trying to do. This are just some of the links that I’ve provided from our education portal.

Thanks and Warm Regards,
Harsh

https://portal.enterprisedna.co/courses/305961/lectures/4883749

9

@Harsh Thank you so much! You have been extremely helpful.

1 Like
10

Hello @agagnon,

You’re Welcome. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh