Forum Discussion

mguy61's avatar
mguy61
Regular Visitor
9 years ago
Solved

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??

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

5 Replies

    • mguy61's avatar
      mguy61
      Regular Visitor
      9 years ago

      Thank you Bhavesh. 

      Warm regards

      Malc

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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's avatar
      mguy61
      Regular Visitor
      9 years ago

      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's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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