Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |