Forum Discussion
Sum values based on distinct values on another column and year
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 ...
NH
Advocate II
Advocate IIHi,
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