After changing the Budget / Actual Variance in the Expenses section (changed the +/- sign), the Total Row is not giving me the correct Net Result in the Total Budget and Total Actual columns.

For the Total Budget and Total Actual columns the total row calculation should be Revenue - Expenses

For the Variance column the total row calculation should be Revenue + Expenses

Below are the measures alongwith the screenshot of the final results provided for the reference to achieve the results based on the conditions that you’ve specified.

Total Budget - Harsh =
VAR _Revenue =
CALCULATE(
CALCULATE ( [Total Amount Base] ,
Scenario[Scenario] = "BU" ) ,
'Chart of Account V2'[STDC 0] = "Revenue" )
VAR _Expenses =
CALCULATE(
CALCULATE ( [Total Amount Base] ,
Scenario[Scenario] = "BU" ) ,
'Chart of Account V2'[STDC 0] = "Expenses" ) * -1
VAR _Results =
_Revenue + _Expenses
RETURN
_Results
Total Actual - Harsh =
VAR _Revenue =
CALCULATE(
CALCULATE ( [Total Amount Base] ,
Scenario[Scenario] = "AC" ) ,
'Chart of Account V2'[STDC 0] = "Revenue" )
VAR _Expenses =
CALCULATE(
CALCULATE ( [Total Amount Base] ,
Scenario[Scenario] = "AC" ) ,
'Chart of Account V2'[STDC 0] = "Expenses" ) * -1
VAR _Results =
_Revenue + _Expenses
RETURN
_Results
Variance BU AC CalcCol - Harsh =
[Total Actual - Harsh] - [Total Budget - Harsh]

I’m also attaching the working of the PBIX file for the reference purposes wherein a separate folder with the name “Key Measures - Harsh” has been created so that you can refer the measures as well.

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

I was actually working onto that after I posted the first solution since I knew that this requirement will eventually come up.

And there’s actually a problem with the way tables are set-up and relationship’s created between the tables i.e. between “FinancialData” and “Chart of Accounts V2”. Since you’ve created a relationship between these two tables changing of signs is not possible because even though if you specify the condition as -

SELECTEDVALUE( 'Chart of Account V2'[STDC 0] = "Revenue" )

Or

SELECTEDVALUE( 'Chart of Account V2'[STDC 0] ) = "Expenses" )

It’ll still straight away ignore this type of conditions and will not allow you to change the signs because what’s actually happening here is although the measure was written like this for Actuals and same for Budgets -

Total Actual - Harsh =
VAR _Revenue =
CALCULATE(
CALCULATE ( [Total Amount Base] ,
Scenario[Scenario] = "AC" ) ,
'Chart of Account V2'[STDC 0] = "Revenue" )
VAR _Expenses =
CALCULATE(
CALCULATE ( [Total Amount Base] ,
Scenario[Scenario] = "AC" ) ,
'Chart of Account V2'[STDC 0] = "Expenses" ) * -1
VAR _Results =
SWITCH( TRUE() ,
SELECTEDVALUE( 'Chart of Account V2'[STDC 0] ) = "Revenue" , _Revenue ,
SELECTEDVALUE( 'Chart of Account V2'[STDC 0] ) = "Expenses" , _Expenses ,
_Revenue + _Expenses )
RETURN
_Results

But at the back end, it’s evaluating the measure like this -

Total Actual - Harsh =
VAR _Revenue =
CALCULATE(
CALCULATE ( [Total Amount Base] ,
Scenario[Scenario] = "AC" ) ,
'Chart of Account V2'[STDC 0] = "Revenue" )
VAR _Expenses =
CALCULATE(
CALCULATE ( [Total Amount Base] ,
Scenario[Scenario] = "AC" ) ,
'Chart of Account V2'[STDC 0] = "Expenses" ) * -1
VAR _Results =
_Revenue + _Expenses )
RETURN
_Results

And no matter what condition you specify in your formula or measure it’ll ignore everything just because a relationship has been created.

On the forum, in my previous solutions as well, I’ve already suggested - “Never ever create a relationship with the Template Tables” because once you do that and create a relationship that’s where things will start to go downhill. In your case as well, exact that same thing is happening and a active relationship is not allowing you change the signs.

So to achieve the solution I created a new file with the revised data model. Based on the column “Standard Class Key” which is common between the FinancialData table and Chart of Acoounts V2 table, I merged them and imported the columns - “STDC 0”, “STDC 1 BP3 Short Name” and “STDC 1 BP3 Short Name” into the FinancialData table. So this is how your revised FinancialData table looks like -

I’m also attaching the working of the Excel as well as PBIX file for the reference as well as providing a link of the post where I’ve stated why not to create relationship with the “Template tables”.

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

Important Note:

1). To view the transformations, please go through the Power Query where you can check out the “Applied Steps” section.

2). Once you revise your data model you’ll be required to change your measures accordingly, wherever neccesary since it was observed that you’ve used “Measure Branching Technique” at a great scale in your analysis.

Thanks for your feedback. I completely understand and support the ‘template’ methodology; handy if you are using a matrix in Power BI for customized subtotals/category structures (i.e. financial statements).

Unfortunately, the chart of accounts is used as a dimensional table to optimize the data model.
I have an advanced visual licence (Zebra), allowing customized subtotals and totals (rows) within the visuals.

At this stage, I think there is no way to adjust the Result (Total Row) when a relationship exists between the dimensional and the transactional tables.

At this stage, I think there is no way to adjust the Result (Total Row) when a relationship exists between the dimensional and the transactional tables.

And therefore as I already suggested this point in the previous post, in order to overcome this problem I’ve brought the required columns in the “Financial Data” table itself and then carried out the calculations.

Although you’re suggesting that “Chart of Accounts V2” is a Dimension table in your data model. In this case, it’s not, because in the case of customized calculations i.e. the results the way you’re trying it to achieve (+) signs for the expenses side as well. You’ll be required to break that relationship between these two tables because if the relationship exists then it’ll ignore the signs.

If you want, you can create another set of de-normalized tables which acts as a dimension tables to filter out the results but this current approach will not help here to get the totals the way you want it.

Please refer the PBIX file which has already been attached in the previous post.

Can you please tell me while replying here , how do we make the dax code background appear like the one that harsh did here . .I am not getting the hang of formatting option to select . .

Hope my question is clear ,nothing technical at . but basically how to use the forum reply formatting option to show the dax code in a way its there in this answer