Calculate the percentage of column total for each column in a table with slicer

[Chill2020]

I'm new to Power BI and I'm having great difficulty trying to calculate percentages of column totals for each element in a column.  Specifically, my data table looks like this:

Age Cohorts	community	Population
0 to 4 years	Sherbrooke (2443)	8,650
0 to 4 years	Sutton (2446058)	110
5 to 9 years	Sherbrooke (2443)	9,015
5 to 9 years	Sutton (2446058)	120
10 to 14 years	Sherbrooke (2443)	8,280
10 to 14 years	Sutton (2446058)	160
15 to 19 years	Sherbrooke (2443)	9,225
15 to 19 years	Sutton (2446058)	165
20 to 24 years	Sherbrooke (2443)	12,750
20 to 24 years	Sutton (2446058)	125
25 to 29 years	Sherbrooke (2443)	11,230
25 to 29 years	Sutton (2446058)	100
30 to 34 years	Sherbrooke (2443)	10,230
30 to 34 years	Sutton (2446058)	115
35 to 39 years	Sherbrooke (2443)	10,395
35 to 39 years	Sutton (2446058)	160
40 to 44 years	Sherbrooke (2443)	9,190
40 to 44 years	Sutton (2446058)	195
45 to 49 years	Sherbrooke (2443)	9,345
45 to 49 years	Sutton (2446058)	225
50 to 54 years	Sherbrooke (2443)	10,890
50 to 54 years	Sutton (2446058)	305
55 to 59 years	Sherbrooke (2443)	11,275
55 to 59 years	Sutton (2446058)	375
60 to 64 years	Sherbrooke (2443)	10,685
60 to 64 years	Sutton (2446058)	520

 I am trying to create the following, where I attempt to write a DAX formula to calculate the percentage shares:

Age Cohort	Sherbrooke (2443)	% Share of Total	Sutton (2446058)	% Share of Total
0 to 4 years	8,650	5.4%	110	2.7%
5 to 9 years	9,015	5.6%	120	3.0%
10 to 14 years	8,280	5.1%	160	4.0%
15 to 19 years	9,225	5.7%	165	4.1%
20 to 24 years	12,750	7.9%	125	3.1%
25 to 29 years	11,230	7.0%	100	2.5%
30 to 34 years	10,230	6.3%	115	2.9%
35 to 39 years	10,395	6.4%	160	4.0%
40 to 44 years	9,190	5.7%	195	4.9%
45 to 49 years	9,345	5.8%	225	5.6%
50 to 54 years	10,890	6.8%	305	7.6%
55 to 59 years	11,275	7.0%	375	9.4%
60 to 64 years	10,685	6.6%	520	13.0%
65 to 69 years	9,465	5.9%	475	11.8%
70 to 74 years	7,560	4.7%	345	8.6%
75 to 79 years	5,040	3.1%	230	5.7%
80 to 84 years	3,825	2.4%	125	3.1%
85 years and over	4,275	2.6%	160	4.0%
Grand Total	161,325	100.0%	4,010	100.0%

Unfortunately, my DAX formula is not working. I have written the following:

Percentage = CALCULATE(SUM('public census2016_newcols'[total_sex])) / CALCULATE( SUM('public census2016_newcols'[total_sex]), ALLSELECTED('public census2016_newcols') )*100

The formula is calculating percentage shares based on the combined populations of both communities displayed in the tables above.  Can anyone tell me where I am going wrong?  Note: I have a slicer that enables me to select population data for one or many communities at a time.  Thanks!

[v-rzhou-msft · Accepted solution]

Hi @Chill2020 

In addition to  lbendlin 's reply, I think you need to select "Percent of Column Total" in show value as.

And you can build a measure to achieve your goal as well.

Measure = 
Var _Population = SUM('Table'[ Population ])
Var _Total = SUMX(FILTER(ALL('Table'),'Table'[community]=MAX('Table'[community])),'Table'[ Population ])
return
DIVIDE(_Population,_Total)

Result:

You can download the pbix file from this link: Calculate the percentage of column total for each column in a table with slicer

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[lbendlin]

You don't need DAX for that, the matrix visual comes with some (but not all) of the built in formulas that you may know from Excel.

In your case, add the number twice into the Values well, and then for the second one choose "Percentage of Row Total" 

or whatever your plan is here.