Switch Budget Actual Variance and Keep the Correct Total Row NET Value

[Rodrigo_Palomino]

Hi Team,

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

Profit & Loss.pbix (1.6 MB)

Previous post related with this one:

Switch Budget Actual Variance Calculation Revenue Expenses

[Harsh]

Hello @Rodrigo_Palomino,

Thank You for posting your query onto the Forum.

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.

Thanks and Warm Regards,
Harsh

Profit & Loss - Harsh.pbix (1.6 MB)

[Rodrigo_Palomino]

Hi Harsh,

Thank you for the incredible prompt response!

I forgot to mention that the Budget and Actual columns have to be kept as positive numbers.

[Harsh]

Hello @Rodrigo_Palomino,

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 -

And then I created below provided measures to achieve the results -

Total Actuals = 
VAR _Revenue = 
CALCULATE( SUM( 'Financial Data'[Amount] ) , 
    'Financial Data'[Scenario] = "AC" , 
    'Financial Data'[STDC 0] = "Revenue" , 
    DATESYTD( Dates[Date] , "30/6" ) )

VAR _Expenses = 
CALCULATE( SUM( 'Financial Data'[Amount] ) , 
    'Financial Data'[Scenario] = "AC" , 
    'Financial Data'[STDC 0] = "Expenses" , 
    DATESYTD( Dates[Date] , "30/6" ) )

VAR _Results = 
SWITCH( TRUE() , 
    SELECTEDVALUE( 'Financial Data'[STDC 0] ) = "Revenue" , _Revenue ,
    SELECTEDVALUE( 'Financial Data'[STDC 0] ) = "Expenses" , _Expenses ,
    _Revenue - _Expenses )

RETURN
_Results



Total Budgets = 
VAR _Revenue = 
CALCULATE( SUM( 'Financial Data'[Amount] ) , 
    'Financial Data'[Scenario] = "BU" , 
    'Financial Data'[STDC 0] = "Revenue" , 
    DATESYTD( Dates[Date] , "30/6" ) )

VAR _Expenses = 
CALCULATE( SUM( 'Financial Data'[Amount] ) , 
    'Financial Data'[Scenario] = "BU" , 
    'Financial Data'[STDC 0] = "Expenses" , 
    DATESYTD( Dates[Date] , "30/6" ) )

VAR _Results = 
SWITCH( TRUE() , 
    SELECTEDVALUE( 'Financial Data'[STDC 0] ) = "Revenue" , _Revenue ,
    SELECTEDVALUE( 'Financial Data'[STDC 0] ) = "Expenses" , _Expenses ,
    _Revenue - _Expenses )

RETURN
_Results



Variance = 
SWITCH( TRUE() , 
    SELECTEDVALUE( 'Financial Data'[STDC 0] ) = "Revenue" , [Total Actuals] - [Total Budgets] , 
    SELECTEDVALUE( 'Financial Data'[STDC 0] ) = "Expenses" , [Total Budgets] - [Total Actuals] ,
    [Total Actuals] - [Total Budgets] )

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 and Warm Regards,
Harsh

Profit and Loss - Harsh Data.xlsx (3.9 MB)

Profit and Loss - Variance Calculation - Harsh.pbix (776.1 KB)

Financial Reporting Sub Totals are Blank

Hello @LizzieTompsett , Thank You for posting your query onto the Forum. Upon checking the PBIX file it’s found out that there’s absolutely no problem with the DAX part but there’s a problem with the data model and the way the relationships are created between the tables. Firstly, as you suggested that you were following the Financial Reporting course. So in Financial Reporting course, nowhere it’s been showcased that we need to create a relationship between the Fact Table and the Template fi…

Modify Calc within Selected Value logic

Hello @mdalton2100, Thank You for providing the reference PBIX files. The reason why you were not able to achieve the results is because of the way you had designed your data model and created the relationship between the tables. i.e. between “Gross Margin Transactions” table and “Mapping For Flux Analysis Only” table. Since your “Gross Margin Transactions” table is a Fact Table and “Mapping For Flux Analysis Only” table is being used as a Template Table. Why do you want to create a relations…

[EnterpriseDNA]

Thank you so much for that detailed answer @harsh

We hope this helped you @Rodrigo_Palomino

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

[Rodrigo_Palomino]

Hi Harsh,

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.

[Harsh]

Hello @Rodrigo_Palomino,

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.

Thanks and Warm Regards,
Harsh

[Dhrubojit_Goswami]

Hi Team,

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

Like this

[Harsh]

Hello @Dhrubojit_Goswami,

Click onto the below highlighted option and the DAX code will be formatted accordingly.

Thanks and Warm Regards,
Harsh

[Dhrubojit_Goswami]

Thank you sir 
Thanks for the quick help