DAX Measure which selects one of 2 measures and can calculate based on row level values

[Anonymous]

Hi all,

Situation is like this:

I have Profit and Loss data which is used in an Income Statement. See below.

The data is split up as follows : We have profit Centers (PC) which are either cars, or not cars.

In the example below 536 is a car and is defined in the profit center table as "cars". The other ones are "non-Car" profit centers. 

The user wants to recalculate the income statement so that he can see the full income statement only with Car profit centers. 

If the actual row data has a posting/row with the Car Profit Center, then the measure should just show the actual line item data. So the 64.460.

If the rows are posted with profit centers that are "not cars" then it should select a measure which recalculates the remaining amount (so the amount where a non car PC is used, in this case the amount 5.797.847 & 1.125.102) and multiplies it by a % that is based on reported volumes and + the amount 64.460.

These volumes are stored seperately and are not connected to the P&L table. Below you can see an example of what I mean with the volumes and % and how the split would go.

But here my measure for "IS Amount per car type" just uses the total amount for that account, and multiplies it by the % based on volumes reported. But it does not calculate it for the Total amount minus correct amount already posted on car profit centers. 

What I would want the measure to do is :

64.460 which is the amount already posted on car profit center + a measure which takes the remaining amount, so 6.922.949 (6.987.409 - 64.460) and multiplies that by the % based on the volume.

So below you can see the measure which I am trying to use now:

test =

IF(SELECTEDVALUE('Dim PC Mapping'[Car or non car])="Cars";[P&L_Total_Converted Monthly];[P&L_Converted_IS_Monthly per % volume Plant])

P&L_Converted_IS_Monthly per % volume Plant = CALCULATE([P&L_Converted_IS_Plant_Monthly];FILTER('Dim PC Mapping';'Dim PC Mapping'[Plant or NSC]="Plant"))*'Fact Stock data Plants'[Stock data Plant Volume % per PC compared to total]

[sturlaws]

Hi @Anonymous 

did you solve this?

If not, you need to create a separate measure for your total. And then use an IF-statement to choose the right measure, depeding on it the row is a total line or not.

The separate measure for the total could look like this:

TestTotal =
SUMX ( SUMMARIZE ( 'Table'; 'Table'[PC] ); [test] )

and create a new measure to switch between the [testTotal] and [test]:

NewMeasure =
IF ( HASONEFILTER ( 'Table'[PC] ); [test]; [testTotal] )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.