Forum Discussion
Percentage Calculation
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
Hi Anonymous
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
1 Reply
Hi Anonymous
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