Financial Reporting(Dax formular combination to template design)

DAX DAX Calculations
1

Hello All,
I need assistance related to the financial reporting w/power bi course. I will be grateful when anyone has time to check the PBix file I attached.

I am having difficulty with the Selected Year Actuals. When I started the first part of the formula for the totals in the income statement, it worked well. (See screenshot 1 and 1.2)
And then, when I try to get the remaining details of the income statement by adding the CALCULATE formulae, there are no changes that are taking place… (see screenshot 2).
The entire income statement detail should appear as Sam Mckay instructed on the video tutorial, but I don’t know where I get things wrong.

Financial Reportingn Solution.pbix (646.1 KB)

Screanshot 1

image
image940×302 25.8 KB

Screanshot 1.2

Screenshot 1.2
Screenshot 1.2522×693 12.7 KB

Selected Year Actuals =
VAR CurrentItem=SELECTEDVALUE(‘Profit and Loss Template’[Items (Normalized)])
RETURN
SWITCH(TRUE(),
CurrentItem=“Total Revenues”,DIVIDE([Revenue],1000,0),
CurrentItem=“Total COGS”,DIVIDE([COGS],1000,0),
CurrentItem=“Total Gross Profit”,DIVIDE([Gross Profit],1000,0),
CurrentItem=“Gross Profit %”,FORMAT([Gross Profit Margin],“0.00%”),
CurrentItem=“Total Other Expenses”,DIVIDE([Other Expenses],1000,0),
CurrentItem=“Total Net Profit”,DIVIDE([Net Profit],1000,0),
CurrentItem=“Net Profit %”,FORMAT([Net Profit Margin],“0.00%”,
BLANK()))

screanshot 2

Screenshot 2.1
Screenshot 2.11136×293 28.2 KB

Selected Year Actuals =
VAR CurrentItem=SELECTEDVALUE(‘Profit and Loss Template’[Items (Normalized)])
RETURN
SWITCH(TRUE(),
CurrentItem=“Total Revenues”,DIVIDE([Revenue],1000,0),
CurrentItem=“Total COGS”,DIVIDE([COGS],1000,0),
CurrentItem=“Total Gross Profit”,DIVIDE([Gross Profit],1000,0),
CurrentItem=“Gross Profit %”,FORMAT([Gross Profit Margin],“0.00%”),
CurrentItem=“Total Other Expenses”,DIVIDE([Other Expenses],1000,0),
CurrentItem=“Total Net Profit”,DIVIDE([Net Profit],1000,0),
CurrentItem=“Net Profit %”,FORMAT([Net Profit Margin],“0.00%”,
CALCULATE([Actual (,000)],FILTER(‘Income Statement Data’,‘Income Statement Data’[Items]=CurrentItem))))

2

Hello @Tala,

Thank You for posting for query onto the Forum and most importantly for providing clear and concise explanation about the problem as well as providing the working PBIX file for the reference. It really becomes much smoother for the forum members to provide assistance in a better and efficient manner.

Now, coming to the solution. The reason why it didn’t yielded the results at an individual row level is because “You closed the BRACKET at the wrong end” or should I say, “You didn’t closed the BRACKET at the right end”.

In your measure for the “Selected Year Actuals” refer the line/variable for “Net Profit %”. It’s like this -

CurrentItem = "Net Profit %", FORMAT ( [Net Profit Margin], "0.00%" ,

So you haven’t closed the bracket after writing the “FORMAT()” function for “Net Profit %” variable and this bracket was directly applied at the end of formula and therefore, it started to give “BLANKS” as a result at an individual row level.

Ideally, it should have been like this -

CurrentItem = "Net Profit %", FORMAT ( [Net Profit Margin], "0.00%" ) ,

Just because of that “BRACKET ERROR”, it started to give incorrect results. Once, this is corrected, you’ll observe that results will start to flow in at an individual level. Below is the screenshot of the final results provided for the reference -

Final Results
Final Results960×879 41.7 KB

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

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

In hindsight, you can say that it was a “very small error” but it definitely gave you a tough time. Even I started to scratch my head at one point of time thinking that where the error is actually committed.

Thanks and Warm Regards,
Harsh

Financial Reporting Solution - Harsh.pbix (647.9 KB)

2 Likes
3

Hello @Harsh,

Thanks a lot for your quick response. I highly appreciate it; you made me feel more fulfilled as an Enterprise DNA member.
The problem is now resolved.

Kind regards

Mutala

1 Like
4

Hello @Tala,

You’re Welcome!!!

I’m glad that I was able to assist you. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh