Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to calculate percentage of totals by year.
For example
Contract Year Policy Type Amount %
No. 1 2016 Default 50,000 40%
No. 2 2016 Default 75,000 60%
Total 125,000 100%
No. 3 2017 Default2 60,000 50%
No. 4 2017 Default2 60,000 50%
Total 120,000 1005
I've tried DIVIDE(SUM(Amount), CALCULATE(SUM(Amount), ALLSELECTED(Year))
The context has to be wrong since the value that is returned is 1.0
Solved! Go to Solution.
try it:
CALCULATE(SUM(Table1[Amount]) / CALCULATE(SUM(Table1[Amount]); ALLEXCEPT(Table1; Table1[Year]; Table1[PolicyType])))
Hi @tlenzmeier,
Try this code:
% Per Year = DIVIDE(CALCULATE(SUM(Table1[Amount]); ALLEXCEPT(Table1; Table1[Year])); CALCULATE(SUM(Table1[Amount]); ALLSELECTED(Table1)))
Ricardo,
Thank you for your reply. I neglected to mention that I need the percentages by policy type and year. So for year 1, policy type = default, I need 60% and 40% and for default1, I need 50% and 50%
try it:
CALCULATE(SUM(Table1[Amount]) / CALCULATE(SUM(Table1[Amount]); ALLEXCEPT(Table1; Table1[Year]; Table1[PolicyType])))
This worked! I only had to add one more column to the ALLEXCEPT; namely VendorID.