locked
Using AverageX() for multiple measures RRS feed

  • Question

  • I currently have an AverageX function based over project Id's. The formula I'm using is this: (AverageX(Values(Project Id), [AssemblyStartCompleteness])).  

    The measure inside there is just two sums being divided.

    I also have two other measures (Part1Completeness and Part2Completeness). These are the same as the sum measures but broken down to each part type. The percentage adds up to the earlier described measure. 

    My issue is that I'd like to use the AverageX fucntion on these measures as well and then use them in a visual where these two make up a column and the above measure is a total line. However, when I'm doing this, My data is averaging over 100%. Previously when I was using the Sum measures, the values would never exceed 100%, which makes sense because there are never scenarios where that would be possible.

    So I'm not sure if I'm going about using these averageX fucnitons correctly but the purpose was to get an average of sums instead of the total column giving just the total average. Alone it was working fine, but when I tried to break them up into the two part measures it no longer works as intended.

    Monday, February 3, 2020 10:12 PM

Answers

  • The averages will work this way if any of the two parts for any of the projects haven't been started (blank completeness). Blank values are excluded when averaging, and the average over the non-blank portion of the set is greater than its fair share that complements the other average to 100%.

    The obvious solution is to force zero values into the calculations instead of blanks, but the hard part is deciding which blanks to zero (think projects with both parts blank).


    Expect me to help you solve your problems, not to solve your problems for you.

    • Marked as answer by jshinnenkamp Tuesday, February 4, 2020 4:36 PM
    Tuesday, February 4, 2020 7:18 AM