Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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??
Solved! Go to Solution.
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.
Best Regards,
Angelia
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
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 category | Year |
Health and Safety | 2015 |
Wages | 2015 |
Working Hours | 2015 |
Health and Safety | 2015 |
Wages | 2015 |
Wages | 2015 |
Working Hours | 2016 |
Wages | 2016 |
Health and Safety | 2016 |
Health and Safety | 2016 |
Wages | 2016 |
Working Hours | 2016 |
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 category | 2016 - 2015 |
Health and Safety | 0 |
Wages | -1 |
Working Hours | 1 |
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
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.
Best Regards,
Angelia
Please check this blog at HERE to calculate YoY difference.
Thank you Bhavesh.
Warm regards
Malc