Actual vs budget Gross Profit

[VincenzoChean]

Hi, 

I have these tables

1) Actuals ( shows the actuals for the month/year in branches in different GL codes)

2) Budget ( shows the budget  for the month/year in branches in different GL codes)  

3) GL codes with categories( categories are Income/Expenses/Operating expenses) for each GL codes. 

4) branch ( different branch codes/name)

what i want to achive is similar to the below matrix. But I want to add Gross Profit for actual & budget.

Gross profit = Income - Cost of sales. 

How can i do this please. 

[danextian]

This can probably be done using Calculation Groups in Tabular Editor. Can you please post a download link to your pbix?

[VincenzoChean]

please see if you can download the pbix

[VincenzoChean]

actual/budget 

[danextian · Accepted solution]

Hi @VincenzoChean ,

Here's my solution: https://drive.google.com/file/d/1Q6fheFcoEiL8dON959zf1FwUBc8T0M7S/view?usp=sharing

I ended up not using calculation groups but instead use a disconnected table to assist with the calculation - disconnected as it doesn't have any relationship to the facts or dimension tables.

[VincenzoChean]

thanks alot. i thought of doing a Category would do what i want. 

but why dont we need to relationship with other the flat tables ?

[danextian]

If you add a relationship between the Category columns in the helper table and GL Codes table, Gross Profit will appear as blank/null so you'll end up with just the same values before adding the helper table - no GP there as Gross Profit row value doesn't exist in GL Codes.  Try creating the relationship and you'll see what I mean.