cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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").

 

2 REPLIES 2
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

 

Regards

Highlighted
chrismark176 Frequent Visitor
Frequent Visitor

Re: Repeat Survey, Year to Year Comparison

@v-ljerr-msft,

 

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

 

https://community.powerbi.com/t5/Desktop/Year-over-year-Survey-comparison/m-p/203906#M89742

 

Hope you can help with answering this issue.