Forum Discussion

aggarwal25's avatar
aggarwal25
Frequent Visitor
9 years ago

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                 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.

Need Help

2 Replies

  • NH's avatar
    NH
    Icon for Advocate II rankAdvocate II
    9 years ago

    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