Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Trailing Average & Previous Quarter Average

I have this data:

 

What I'm trying to do with this data is:

 

1. Figure out how to make trailing 12 month average on my [Positive %] measure. It is a measure that simply goes through my fact table and counts the number of what's considered positive feedback:

 

 

2. Figure out how to calculate what the average positive % was for the previous quarter (Qtr 1) using the trailing 12 month averages that have been calculated.

 

I have an inactive relationship setup between my fact and date table. It's set up this way so other formulas from other tables are not impacted. I've tried setting up a 1 to many relationship, but it causes other pieces to break so I'd rather not do it that way if I can help it. Thanks!

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

    • 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.