Forum Discussion

hnguy71's avatar
hnguy71
Super User
6 months ago
Solved

Average of Average Pattern Inaccuracies

Hi all,
Getting a bit stumped on an average inaccuracy that I'm facing maybe someone can shed some light on a pattern that I may be missing.

Scenario: Creating an average completion calculating based on set criteria
Challenge:  Average seems to be correct at the criteria level, but at sub-total level seems incorrect.

 I have the following measures in the model:

CurrentRelease = 

// Select a release ID, if none is selected, return the max ID of an active release
VAR _ID = CALCULATE(MAX(Dim_Criteria[cID]), Dim_Criteria[Active] = "YES", ALL(Dim_Criteria))
VAR _Release = CALCULATE(MAX(Dim_Criteria[Release]), Dim_Criteria[cID] = _ID)

RETURN

_Release
t.Required = 

// Based on selected training release, return total number of required training executions.

VAR _Release = [CurrentRelease]
VAR _Required =  CALCULATE(SUM(Fct_Achieved[Required]), Dim_Criteria[Release] = _Release)

RETURN

_Required
t.Completed = 

// Based on selected training release, return total number of completed training executions.

VAR _Release = [CurrentRelease]
VAR _Completed =  CALCULATE(SUM(Fct_Achieved[Completed]), Dim_Criteria[Release] = _Release)

RETURN

_Completed
t.Average = DIVIDE([t.Completed], [t.Required])
Avg.Of.Avg = AVERAGEX(Dim_Criteria, [t.Average])

 

By using the average of the criteria [Avs.Of.Avg], I seem to be getting the correct value that I'm looking for:

 

However, the moment I add additional attributes such as user geography data, the numbers begin to look strange:

 

 

For example, coaching criteria, I am expecting 89.52% at sub-total level but the average of those seems incorrect. If I average out coaching (91.91 + 96.01 + 82.43 + 100 + 92.86 / 5 = 92.64%)

If I spin it a bit differently where I'm anazlying by Geography data, similarly, the total average is incorrect against the row context:

 

To be honest, I'm not sure which average number is correct, possibly none of it, however, the end-goal is to allow this data to be exported or used in a sub-model. When building a debug table, the average total says 92.54%:

Is there a DAX pattern that I'm not seeing or building correctly? Any guidance would be greatly appreciated. I've attached a sample pbix to the post.

 

 

  • hnguy71 You can account for the different granularities using the ISINSCOPE function. So something like a SWITCH TRUE statement combined with ISINSCOPE functions should allow you to get exactly what you want. 

8 Replies

  • hnguy71 The "correct" answer depends on what you want. Do you want the average or the average of averages? For example, take the following data:

    Column1Column2

    Red .5
    Red .5
    Red .5
    Blue .75
    Blue .75
    Green .3
    Green .3
    Green .3
    Green .3
    Green .3
    Green .3
    Green .3

     

    If you put this into a table visual and use the average aggregation, you get the following:

     

    .43 is the average of all of the base fact table rows. However, if you compute the average like ( .75 + .3 + .5 ) / 3 you get an average of .517. If you want the latter value then you need to build a table variable in your DAX that is essentially summarized the same way as your table visual and then use AVERAGEX across that table.

    • hnguy71's avatar
      hnguy71
      Super User

      Hi GeraldGEmerick 

      Thanks for your quick reply and insights.

       

      I do definitely know that I want the average of the values in context ( Avg = sum of values / total number of values ). I can definitely build out a measure using a virtual table and use AVERAGEX on top of that, but I'd have a slight concern.

       

      1.  It would be unsustainable to build a measure per visual per granularity
      2. The average will be slightly different at each granularity

      Knowing this, how would you recommend to ensure that the 'roll-up' at each granularity still adds up to the top?

      • GeraldGEmerick's avatar
        GeraldGEmerick
        Super User

        hnguy71 Are you able to provide sample data for this? How many different roll-ups are we talking about? The average of the average would not necessarily be different at each granularity. The subtotal for each granularity would be correct in terms of how you are computing the average of the average. You could also apply the average of the average from the top down to ensure you were not getting different results between the top level of granularity and the sublevel subtotal.