Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
aggarwal25
Frequent Visitor

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.

2 REPLIES 2
Eric_Zhang
Microsoft Employee
Microsoft Employee

@aggarwal25

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

 

Capture.PNG

NH
Advocate II
Advocate II

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

 

survey data table.JPG

 

 

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.

result in table.JPG

 

Cheer,

NH

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.