Showing results for 
Search instead for 
Did you mean: 
jminner Occasional Visitor
Occasional Visitor

Repeat Survey, Year to Year Comparison

I've seen a couple year to year comparisons, but none seem to match my format.  I appreciate any help that can be provided.


I have a survey that we provide to users each year.  Questions do not change, but number of respondents changes.  I have imported each Excel spreasdsheet as a unique dataset query.


2015 or 2016 Results can be graphed individually using count or % of Grand Total with no issues from their respective queries:



Primary Work Location 2015.JPGPrimary Work Location - 2015Primary Work Location 2015 per.JPG

I apended the 2016 rows to the 2015 rows to create a third query, which allowed me to display year to year response counts in the same clustered column chart:


Primary Work Location combined.JPG

But graphing them together provides a calculation error when I try to use % of grand total (grand total incorrectly being the total number of rows for both years):

Primary Work Location combined-per.JPG

How do I get the left column to only calculate percent against 2015 rows, and the right column to calculate against 2016 rows?


Here's an example of Excel chart from last year:

2015 Primary location graph.JPG


In my queries, the first column is the Year of the results (currenty called "Start Date").


v-ljerr-msft Super Contributor
Super Contributor

Re: Repeat Survey, Year to Year Comparison

Hi @jminner,


According to your description, you may need to create a measure to calculate the  % of Grand Total in this scenario. Could you post your table structure with sample data in this case? It's better to share a sample pbix file. So that we can help with the measure.Smiley Happy



chrismark176 Frequent Visitor
Frequent Visitor

Re: Repeat Survey, Year to Year Comparison



I have the same question. There is a copy of my data set here:


Hope you can help with answering this issue.