DAX formula for financial report

[MarieD]

Hi all,

i'm working on a financial report in PowerBI and i already had a lot of inspiration from the internet, but there is one problem now and I really don't know how to sort this.

I have caclulated some subtotals, for "omzet", 'Brutowinst" and 'Operationele kosten'. this all works. Then for all other costs, i have made a calculation called 'Actuals'. This also works fine.

Now i have an Financial template with RowID to make sure the table will be in the correct sorting order and to make sure the row names will look properly with some spaces before descriptions. So this is my model:

I'm told that you should not link the financial template to the rest of the tables, as it is only used for rownames and the values will have to be calculated and not linked. 

Then, i have made a DAX formules that should decide in the financial template table (on the right) if there is a subtotal calculated or not. If yes, it should return the subtotal amount (this works fine), if no, it should give the 'actual' amount (as in the table on the left). But as you can see, it returns the total of the acuals almost everywhere, and not the individuals actuals for each row. But what i want, is a subtotal and otherwise the acual amount fot that specific row. 

These are the formulas I have used now:

for the actuals:

for the Selected Years Actuals:

Probably I made a mistake in the CALCULATE part at the end? But I don't know how to fix it, so i hope somebody can help me with this?

It would be much appreciated!

Kind regards, Marieke

[MarieD]

Apparently i had to add a FILTER formula to the CALCULATE part of this formula. to make sure it linked to the correct items with currentitem. Now it works as i expected! 

[Greg_Deckler]

@MarieD - First of all, you do not need the CALCULATE functions in the formulas you presented. Those are not doing anything for you because you are not trying to switch context. So I would get rid of them.

Second, your two tables are not related to one another (Financials and Template WV) This is why you are getting the total value every time, because there is no filtering going between the tables. You either need to create that relationship or create the relationship within the measure (Actuals).

Sample data and expected output would be great. Please always first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Fowmy]

@MarieD 

Yor currentitem value inside the selected value is not filtered in the visual on the right that is why it shows the total amount in your cases Actuals. But, the table has that field and It correctly shows the amount.

Either you need to add a column in the table which has the value "OMZET" and so on or insert a filter on the page if you want to select for the whole page to be affected.

Share your file with sample data using one drive or google drive.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn