Forum Discussion
Divide by Total
The issue is actually VERY visible in your matrix.
On the first row, REVENUE is avaiable, but not COST.
On the 2nd to 4th rows, COST is available. while REVENUE not.Therefore your DIVIDE will always have one less value.
What you need to do is to make REVENUE available on the 2nd to 4th rows.
I can't see the model but looks like the "rows" of your tables are "Deparment"? Anyway, let's imagine that your table called "Table" and the rows you're showing are Departments column.
Delta_Cost_Rev=DIVIDE([Cost pos];Calculate([Revenue];REMOVEFILTER(Table[Department]));0)
(if you don't have the very latest version of PowerBI replace REMOVEFILTER with ALL)
What you have to do is to allow Dax to exit from the filter context of those 2nd to 4th rows and see the value of revenues in the 1st row. And you do that by removing the filter of the department.
Last thing: when you use DIVIDE, always put the default value (in this case 0) as the last parameter.
The issue is actually VERY visible in your matrix.
On the first row, REVENUE is avaiable, but not COST.
On the 2nd to 4th rows, COST is available. while REVENUE not.
Therefore your DIVIDE will always have one less value.
What you need to do is to make REVENUE available on the 2nd to 4th rows.
I can't see the model but looks like the "rows" of your tables are "Deparment"? Anyway, let's imagine that your table called "Table" and the rows you're showing are Departments column.
Delta_Cost_Rev=DIVIDE([Cost pos];Calculate([Revenue];REMOVEFILTER(Table[Department]));0)
(if you don't have the very latest version of PowerBI replace REMOVEFILTER with ALL)
What you have to do is to allow Dax to exit from the filter context of those 2nd to 4th rows and see the value of revenues in the 1st row. And you do that by removing the filter of the department.
Last thing: when you use DIVIDE, always put the default value (in this case 0) as the last parameter.