Hi All you excellent excel wizards again!
A bit of a new one here, that's had me hung up for a few days!
So I now hove my % Profit/Loss figure, we split our work into departments...
I have the actual recovery calculating correctly the sum for this is :
=(Sum('Xero Income'[Credit])-Sum('Harvest Expenses'[Amount])-Sum(Harvest[Time Cost]))/MAX(Sum('Xero Income'[Credit]),SUM('Harvest Expenses'[Amount])+SUM(Harvest[Time Cost]))+1
But due to the +1 to get the correct recovery, it adds 100% to clients that are in the payroll department and do not have anything to do with bookkeeping, bypassing my department filter and gives everything that's blank in the expense and income tables (as they're filtered by department! in the pivot) the 100% result bypassing the filter... the blank rows are due to the expenses and income being in a different department "payroll" i want this 100% to go away pretty much! so i don't get all these blank rows due to it being the wrong department.
I need it to only calculate IF the department = "Bookkeeping" for example, My pivot is linked to a department via: (all tables software expenses / Time expenses / income are connected by this link )
Any help would be appreciated, because its causing issues such as all these blank cells:
Many many Advanced thanks!
A bit of a new one here, that's had me hung up for a few days!
So I now hove my % Profit/Loss figure, we split our work into departments...
I have the actual recovery calculating correctly the sum for this is :
=(Sum('Xero Income'[Credit])-Sum('Harvest Expenses'[Amount])-Sum(Harvest[Time Cost]))/MAX(Sum('Xero Income'[Credit]),SUM('Harvest Expenses'[Amount])+SUM(Harvest[Time Cost]))+1
But due to the +1 to get the correct recovery, it adds 100% to clients that are in the payroll department and do not have anything to do with bookkeeping, bypassing my department filter and gives everything that's blank in the expense and income tables (as they're filtered by department! in the pivot) the 100% result bypassing the filter... the blank rows are due to the expenses and income being in a different department "payroll" i want this 100% to go away pretty much! so i don't get all these blank rows due to it being the wrong department.
I need it to only calculate IF the department = "Bookkeeping" for example, My pivot is linked to a department via: (all tables software expenses / Time expenses / income are connected by this link )
Any help would be appreciated, because its causing issues such as all these blank cells:
Many many Advanced thanks!
