How to calculate variance when amounts are all in 1 column

[rpinxt]

So this is the situation:

First the source tables:

Cost Center from a master data table

Actuals come from a table with our SAP actuals

LBE1 (and the other LBE's) comes from an Excel source table.

They both have cost center so cost centers binds the 2 tables.

Both tables have the same layout.

First column is 'Type' and you can see the options. Also there is 1 column called Amount and is regulated in the rows on Type what the amount is for.

So this is a matrix with cost center in the rows and type in the columns. Amount is in values.

Now I would want Actuals -/- LBE1. And of course when I select Actuals and LBE2 I want to see that variance.

However I think when I make a measure it will always pop up between actuals and LBE and behind LBE.

So I also tried using a table as visual but then amount of actual and LBE will be together....

Did we screw ourselves with this layout? On paper it seemed to be the best option to have a fields Type so we did not need a column for all types of amount. Now I am not so sure anymore...

[rpinxt · Accepted solution]

Guessing it cannot be done in that way then.

Fix it differently. Made the 4 different LBE calculations and then put them in a SWITCH function.

LBE = SWITCH([Selected LBE],

    "LBE1", [LBE1],

    "LBE2", [LBE2],

    "LBE3", [LBE3],

    "LBE4", [LBE4]

)

So depending the choise of the user it will display the corresponding LBE.

This way it is also easy to calculate the variance by just do Actuals -/- LBE.

PS: Is it still not possible to have dynamic column headers? ie. when for LBE, LBE1 is selected that the header for this column also would be LBE1?

[rpinxt]

Ok looking at doing this as a list.

Now I found a way to get the LBE that I selected:

So this will get me the correct LBE that has been selected.

Now I want to put this in my list:

As you see that actuals was rather easy because it is Amount where Type = Actual:

Actuals = CALCULATE([Amount],'AVN MD Type'[Type]="Actuals")

Now wanted to do somethig similar for LBE selected. But error I did not encounter before:

I cannot link the Type to my measure that gives me the correct LBE value (LBE1)?

Or is there another way in this calculate to make the output variable for selected LBE.