Sum values based on distinct values on another column and year

[aggarwal25]

Hi

I have a large survey data form 2014-2017 and I want to calculate how many responses were received each year (both count as well as %) by year and show the same in my BI dashbaord. 

 Year                 Response                        Count fo Y/N

2014                    y                                    Sum of all yes in 2014

2014                    n                                    Sum of all no in 2014

2015                    y                                    Sum of all yes in 2015

2015                    n                                    Sum of all no in 2015

Thanks for help.

[Eric_Zhang]

@aggarwal25

You can also use a Matrix visual without creating any measure.

[NH]

Hi,

Unable to attach the pbix file. Pls see the photos attach and hope this is what you are looking for.

1. Created dummy survey table1

2. Created 3 measured as follows:

Count of Y/N = CALCULATE(COUNTA(Table1[Responsed (Y or N)]),FILTER(Table1,Table1[Year]=Table1[Year]))

Total responsed by year = CALCULATE(COUNTA(Table1[Responsed (Y or N)]),ALLEXCEPT(Table1,Table1[Responsed (Y or N)]))

% Responsed by Yr = DIVIDE([Count of Y/N],[Total responsed by year])

 3. Created the result using Table Preview visualizations.

Cheer,

NH