Line chart using weighted survey data

[Anonymous]

Really new to Power BI -- apologies if this is a stupid question.

I have data as below. 

sampling weight	category	year
0.8	a	1
1	b	2
1.2	a	3
0.7	b	1
1	a	2
1.3	b	3
0.9	a	1
1	b	2
1.9	a	3

I want to create a line chart showing the percent (accounting for the sampling weights) in each category over time. I have been able to get the output I want as a matrix by putting year as column, category as row, and sampling weight as values. But I can't figure out how to turn this into a line chart.

I have tried creating the following measures:

weightsum=sum('table'[sampling weight])

catsum=calculate([weightsum]), allexcept('table', 'table'[category])

pccat=divide([weightsum], [catsum], 0)

and using year for axis, category for legend, and catsum for values. But this gives me lines with each point being a percentage of the grand total and not for each year. I'm guessing I need to put the year into one or more of the measures I calculated (weightsum, catsum, pccat) but I'm not sure how. 

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

You may try to modify your catsum measure if you want to calculate a percentage of each year.

catsum = calculate([weightsum],ALLEXCEPT('Table','Table'[year]))

You can check more details from here.

Best Regards,

Stephen Tao

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

[amitchandak]

@Anonymous , Not clear to me. refer if this can help

weightsum=sum('table'[sampling weight])
catsum=calculate([weightsum], all('table', 'table'[category]))
pccat=divide([weightsum], [catsum], 0)