Percent of Group Totals

[tlenzmeier]

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

[ricardocamargos]

Hi @tlenzmeier,

Try this code:

% Per Year = DIVIDE(CALCULATE(SUM(Table1[Amount]); ALLEXCEPT(Table1; Table1[Year])); CALCULATE(SUM(Table1[Amount]); ALLSELECTED(Table1)))

[tlenzmeier]

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%

[ricardocamargos · Accepted solution]

@tlenzmeier,

try it:

CALCULATE(SUM(Table1[Amount]) /  CALCULATE(SUM(Table1[Amount]); ALLEXCEPT(Table1; Table1[Year]; Table1[PolicyType])))

[tlenzmeier]

This worked! I only had to add one more column to the ALLEXCEPT; namely VendorID.