add measured /calculated value in matrix row

[Evandam]

Hi,

i have an export of financial data (journal entries). Based on the GL account i have dfined a higher hiearchy level. I allready created a mapping table that contains all the GL accounts with a mapping to the correct hiearchy level.

reference table

mapping table

data model

data model

if i now create a table it looks like this:

1 omzet = revenue

2 KP omzet = Cost of goods sold

as you might notice there is no reference number 3, 6 and also no 9.

reference number 3 should be the gross margin and can be calculated by subtracting total of ref 2 from ref 1.

so 861.320 minus 675.386=185.934

My question comes down to this:

"how can i display reference number 3 in the same table"..

so it should look like this:

what is the best approach for this?

perhaps creation another table in which measures are stored on a row level. Using Calculate + sum in an IF statemet to calculate the total amount per reference number (1,2,4,5,7,8).

Then use the calculated values to (1,2,4,5,7,8) to calculate reference numbers 3, 6 and 9??

3 = 1 minus 2

6 = 3 minus (4+5)

9 = 6 minus (7+8)

Thanks!

Erwin

[Evandam]

update:

calculating the the total amounts based on reference number have worked.

but when i make an active realtion between tables the calculations for reference 3,6,9 disappear

no relation
no relation

active relation

syntax of calculation

Actuals YTD = 
IF('calculations'[reference number]=1;
    CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=1);

IF('calculations'[reference number]=2;
    CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=2);

IF('calculations'[reference number]=4;
    CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=4);

IF('calculations'[reference number]=5;
    CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=5);

IF('calculations'[reference number]=7;
    CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=7);

IF('calculations'[reference number]=8;
    CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=8);

IF('calculations'[reference number]=3;
        CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=1)
        -
        CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=2);

IF('calculations'[reference number]=6;
        CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=1)
        -
        CALCULATE(SUM('Journal Entries'[total amount]);
        OR(OR(Mapping[reference number]=2;Mapping[reference number]=4);Mapping[reference number]=5));



IF('calculations'[reference number]=9;
        CALCULATE(SUM('Journal Entries'[total amount]);Mapping[reference number]=1)
        -
        CALCULATE(SUM('Journal Entries'[total amount]);
        OR(OR(OR(OR(
            Mapping[reference number]=2;
            Mapping[reference number]=4);
            Mapping[reference number]=5);
            Mapping[reference number]=7);
            Mapping[reference number]=8));

0)))))))))

[Evandam]

@amitchandak 
maybe this info will be helpfull to further understand the situation

[amitchandak]

@Evandam , Make sure the reference number is present in the dimension

Then create measure like

Switch( true() ,

Max(Mapping [Reference]) =3,

calculate([measure] , Mapping [Reference] =2) -calculate([measure] , Mapping [Reference] =1)

Max(Mapping [Reference]) =6,

(calculate([measure] , Mapping [Reference] =2) -calculate([measure] , Mapping [Reference] =1) ) -

calculate([measure] , Mapping [Reference] =4) + calculate([measure] , Mapping [Reference] =5)

Max(Mapping [Reference]) =9,

<Add for 9>,

[Measure]

)

It will be a very similar approach like

Power BI Modeling- Unselected values as others - https://youtu.be/HX4pgexYwf4

[Evandam]

Hi @amitchandak 

thanks for your reply.
unfortunatly it is not very usefull in my case.

mapping table is just a table in wich all GL accounts are mapped to reference number (280 rows in total).

So i will not work to just put reference number 3,6 and 9 in this table and then calculate the total amount for these reference numbers via the measure you suggested...

thanks any way:)