Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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")))
We have found the solution:
% vd Omzet = DIVIDE(CALCULATE(sum(Profit_Transactions[Saldo]));CALCULATE(sum(Profit_Transactions[Saldo]);FILTER(ALL(Mapping);Mapping[Kop] = "Omzet")))
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
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |