Showing calculated column in matrix while showing measures as rows

[djillbunters]

I'm trying to create a P&L report in PBI Desktop.

My dataset (high level) exists of a table with actuals (GLAccount, Amount, Date) and a table with budget (GLAccount, Amount, Date).

I combined the two tables and used a column Classification to determine if it's Actual or Budget.

By showing the measures as rows I was able to get this (it's still very early WIP):

My next goal is to add a column "Delta" which is the difference between Actual and Budget, to show whether we are running ahead or behind on schedule.

However I can't seem to figure out how to do this.

As I use the column "Classification" as column in the matrix, I'm assuming I should add calculated rows to the table, in order to show Delta next to Actual and Budget, but I have no idea how.

Maybe I'm just handling this entirely wrong?

The reason I'm using measures for turnover, COGS, EBITDA etc... is because I only have raw data from transactions on each GL Account, as I pull my data from accounting software.

[Ashish_Mathur]

Hi,

One idea would be to create 2 seperate columns (instead of carrying Budget and Actual in the Clasification column) - Budget and Actual in the Query Editor.

[Greg_Deckler · Accepted solution]

@djillbunters If I understand this correctly, you will need to use a disconnected table consisting of rows:

Actual

Budget

Delta

Use that as your column header. Then you will need to modify your measures such as:

Turnover = 
  VAR __Category = MAX('DisconnectedTable'[Column1])
  VAR __Result = 
    SWITCH( __Category, 
      "Actual", SUMX(FILTER('Table', [Classification]="Actual"), [Value]),
      "Budget", SUMX(FILTER('Table', [Classification]="Budget"), [Value]),
      SUMX(FILTER('Table', [Classification]="Actual"), [Value]) - SUMX(FILTER('Table', [Classification]="Budget"), [Value])
    )
RETURN
  __Result

It's essentially a simple version of this: The New Hotness (Custom Matrix Hierarchy) - Microsoft Fabric Community

[djillbunters]

So this worked like a charm.

I'm maybe going too fast here, but is there also a way to create a hierarchy or group within the "Services" for example?

I want to open Services and show all different GL account groups within "Services" separately, but also be able to close it.

Kind of like this:

It looks like hierarchy or group is not possible with measures, but measures seem the only way to calculate gross margin, ebitda etc...

[djillbunters]

Thanks a lot!

I am going to try this first thing tomorrow and will get back to you!