Matrix - Display row value as percentage of another row

[joeysun]

Hi all,

I have a matrix where I have one column with the values for each row. In a second column, I want to display each row as a percentage of the first row.

What I currently have: (it's not working as can be seen)

Quick translation:

Oneindig = Infinity

Saldo = Balance

Omzet = Revenue

Inkopen = COGS
Personeelskosten = Labour costs

Bedrijfskosten = Company costs

,..

The balance is retrieved from a table TRANSACTIONS containing all the transactions, each transaction having an account number. 

The account numbers are grouped in a table called "Mapping" and maps each account number to a group (Revenue, etc.)

The measure I used is:

% of the Revenue = CALCULATE(SUM(Transactions[Saldo])) /  CALCULATE(SUM(Transactions[Saldo]); Mapping[Kop] = "Omzet")

Probably since the other rows in the table are mapped to a different group (not revenue), the total balance for revenue in those rows are zero. That explains the infinity since dividing by zero is not possible. 

How can I fix this problem? Thanks in advance! 

[Basvanbaar · Accepted solution]

We have found the solution: 

% vd Omzet = DIVIDE(CALCULATE(sum(Profit_Transactions[Saldo]));CALCULATE(sum(Profit_Transactions[Saldo]);FILTER(ALL(Mapping);Mapping[Kop] = "Omzet")))

[v-ljerr-msft]

Hi @joeysun,

Could you try the formula below to see if it works in your scenario? 

% of the Revenue =
DIVIDE (
    CALCULATE ( SUM ( Transactions[Saldo] ) );
    CALCULATE (
        SUM ( Transactions[Saldo] );
        FILTER ( ALL ( Mapping[Kop] ); Mapping[Kop] = "Omzet" )
    )
)

Regards

[Basvanbaar]

Hi @v-ljerr-msft,

Thanks for your reply! I'm working together with @joeysun on this project.

I just tested your proposed solution, but unfortunately it returns the same results (only difference is blanks instead of infinity).

Any other suggestions would be most welcome!

Kind regards