cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mguy61 Frequent Visitor
Frequent Visitor

Year on Year data comparison

I have a simple table of survey data arranged in rows with one resonse per row, the total number of rows equalling the total number of reponses. There is a Year column containing either 2015 or 2016 and a question column indicating the survey question. I want to be able to calculate the difference in the number of responses (rows) between 2015 and 2016 for each question, or group of questions.

I then want to plot the year on year difference on a graph where a decrease shows beneath the X axis. I guess I need to create a measure but can't find any help in how to do this. Can you help??

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: Year on Year data comparison

Hi @mguy61

Based on your description, you can use COUNTROWS or COUNTA function to calculate the number of category in each year. I try to reproduce your scenario using sample data and get expected result.

Please create a measure using the formula below.

this-last = CALCULATE(COUNTA(Test1[Observation category]),FILTER(ALL(Test1[Year]),Test1[Year]=MAX(Test1[Year])))- CALCULATE(COUNTA(Test1[Observation category]),FILTER(ALL(Test1[Year]),Test1[Year]=MAX(Test1[Year])-1))


Create a table, select the Observation category and measure as values, you will get the desired result.

Capture1.PNG

Best Regards,
Angelia

5 REPLIES 5
Super User
Super User

Re: Year on Year data comparison

Please check this blog at HERE to calculate YoY difference.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
mguy61 Frequent Visitor
Frequent Visitor

Re: Year on Year data comparison

Thank you Bhavesh. 

Warm regards

Malc

v-huizhn-msft Super Contributor
Super Contributor

Re: Year on Year data comparison

Hi @mguy61,

For your reguriement, please post more details or sample data for further analysis. If you find the solution, please share or mark the correnponding reply as answer for help others easily. Thanks a lot.

Best Regards,
Angelia

mguy61 Frequent Visitor
Frequent Visitor

Re: Year on Year data comparison

Hi Angelia

 

We make observations on farms when we see something wrong, looking at over 40,000 farms. Each observation is logged against a category (also Farmd ID, Auditor name, location, month, year). The table below is a simplified example showing just the year. Each row corresponds to an observation. 

 

Observation categoryYear
Health and Safety2015
Wages2015
Working Hours2015
Health and Safety2015
Wages2015
Wages2015
Working Hours2016
Wages2016
Health and Safety2016
Health and Safety2016
Wages2016
Working Hours2016

 

What I want to do is to create a table within PowerBI that calculates the difference in the number of observations for 2016 (COUNTROWS??) compared with 2015, for each observation category. So the resultant table would look something like this:

 

Observation category2016 - 2015
Health and Safety0
Wages-1
Working Hours1

 

I would want to visualise the data by auditor name, location etc, using the calculated table.

 

I have been trying to create a calculation by creating a New Table and applying filters but have not had any success.

 

Thanks

 

Malcolm

v-huizhn-msft Super Contributor
Super Contributor

Re: Year on Year data comparison

Hi @mguy61

Based on your description, you can use COUNTROWS or COUNTA function to calculate the number of category in each year. I try to reproduce your scenario using sample data and get expected result.

Please create a measure using the formula below.

this-last = CALCULATE(COUNTA(Test1[Observation category]),FILTER(ALL(Test1[Year]),Test1[Year]=MAX(Test1[Year])))- CALCULATE(COUNTA(Test1[Observation category]),FILTER(ALL(Test1[Year]),Test1[Year]=MAX(Test1[Year])-1))


Create a table, select the Observation category and measure as values, you will get the desired result.

Capture1.PNG

Best Regards,
Angelia