Forum Discussion

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi Anonymous,

 

Please refer to Desktop/Trailing-12-Month-Average. It will give you some ideas. Can you share a dummy sample? Since the [Positive %] is a measure, I can't recreate it in my test.

 

Best Regards,

Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi v-jiascu-msft,

     

    I attempted to attach a sample of my data but I don't see a place on here to attach spreadsheets. Here's a screenshot of what I'm working with:

     

    So the breakdown:

    1. These are survey results. The columns are questions that were asked in the survey. Values are the answers from the users.

    2. I was first tasked with grouping and counting everything that is considered a "positive" response. Positive responses consist of Strongly Agree, Agree, Happy, Very Happy.

    3. In order to achieve this, I took all columns with these values andn unpivoted them so everything was in one column. This makes it easier to count.

    4. So to get the positive %, I had one measure that counts the "positive values" and one measure that counts all values whether they are positive, negative or neutral. Then simply did [Positive Count/Total Count].

    5. From here, I am stuck on getting what the average % is:

     

     

    It keeps coming out 100% no matter how I set it up. After I figure out how to get the overall average, I then need to figure out how to have this as a trailing 12 month average so I can see how it is trending over time. Then the last step using my trailing 12 month average numbers, is getting the overall average for the previous quarter.