Percentage Calculation

[Stuartm1983]

Hi,
 
I'm trying to pull together a "simple" Daily loss percentage based upon rent loss and total rent due for each area.
 
I am combining two tables: A) a Void table which has a row for each day (Multiple records for each area exist and should be included)
  Date  Area  Loss Amount
 13/09/2020 North £20
 14/09/2020 North £20
 15/09/2020 North £20
 13/09/2020 South £20
 14/09/2020 East  £20
 15/09/2020 East  £20
 15/09/2020 East  £30
 
and table B)
  Area  Target Rent 
  North £100
  South £130
  East  £120
  West  £140
 
The modeller is combined on Area and has Date and Area as a slicer.
 
Effectively I'm requiring a left join from A with B and a percentage sliced on each day calculating where the total void losses are summed up on table A and divided by the joining target amount from B
 
What I'm getting at the moment is:
Sliced on 15/09/2020
 
Area  Loss Amount  Target Amount  % Void Loss
North  £20  £300  Incorrect Amount
East £50  £390  Incorrect Amount
South 
West 
TOTAL £70 £490 14.2%
Using:
 

Daily Target Rent = sum(target_rents_by_scheme[daily_target_rent])

% of Rent Loss = sum('Granular Voids'[Daily_loss])/'Granular Voids'[Daily Target Rent]

 
What I require is:
 
Area  Loss Amount  Target Amount  % Void Loss
North  £20  £100  20%
East £50  £130  38.5%
South  £0  £130  0%
West  £0 £140 0%
TOTAL  £70 £490 14.2%
 
Thanks

[OwenAuger · Accepted solution]

Hi @Stuartm1983 

I would first recommend you set up your data model like this, with Date and Area dimension tables:

Then write measures as follows:

Loss Amount = 
SUM ( 'Granular Voids'[Amount] )

Target Amount = 
COUNTROWS ( 'Date' ) * SUM ( Target[Target Rent] )
// This treats Target Rent as a daily amount and multiplies by the number of days filtered

% Void Loss = 
DIVIDE ( [Loss Amount], [Target Amount] )

Then the measures should produce the values as expected when filtered on 15 September (using your inputs at the top of the post):

PBIX attached for reference.

Regards,

Owen