pgrandits's avatar
pgrandits
Frequent Visitor
4 years ago
Status:
Investigating

AverageX not working

None of my averagex computations are working. 

 

To show my problem, I created 3 sample tables: a dimension table listing Salespersons, a CalendarDates table and a Sales facts table. Each salesperson is assigned to a State. The tables have single direction relationships as you would expect using salespersonID & Date fields. 

 

My averagex formula for computing the average sales per state for all periods is as follows:

AverageX Sales by State=

AverageX(VALUES(Salespersons[State]),sum(sales))

 

My averagex formula for computing the average sales per salesperson for all periods is as follows:

AverageX Sales by Salesperson=

AverageX(VALUES(Salespersons[Name]),sum(sales))

 

I get the exactly same result using AverageX function as if I simpy summed the sales in the sales table as shown below:

 

Why isn't it giving me the averages?

 

Any help would be much appreciated as it is driving me crazy!

 

Data Modeling

2 Comments

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    4 years ago
    Status changed:
    New
    to
    Investigating

    Hi pgrandits 

     

    Measures' results are mainly impacted by the relationships and calculation context in your model. For AVERAGEX itself, it evaluates expressions for each row of a table, and then take the resulting set of values and calculate its arithmetic mean. Please try these measures instead of using AverageX.

    AverageX Sales by Month =

    VAR total_ =

        CALCULATE ( SUM ( Sales[Sales] ), ALL ( Sales ) )

    VAR count_month =

        CALCULATE ( DISTINCTCOUNT ( CalendarDates[Date] ), ALL ( CalendarDates ) )

    RETURN

        total_ / count_month

     

    AverageX Sales by Salesperson =

    VAR total_ =

        CALCULATE ( SUM ( Sales[Sales] ), ALL ( Sales ) )

    VAR count_person =

        CALCULATE ( DISTINCTCOUNT ( Salespersons[Name] ), ALL ( Salespersons ) )

    RETURN

        total_ / count_person

     

    AverageX Sales by State =

    var total_=CALCULATE( SUM(Sales[Sales]),ALL(Sales))

    var count_state=CALCULATE(DISTINCTCOUNT(Salespersons[State]),ALL(Salespersons))

    return total_/count_state

     

    Best Regards,

    Community Support Team _ Caiyun

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    4 years ago

    Hi pgrandits 

     

    May I know whether you have tried these dax formulas? Will you get the expected results with them?

     

    Best Regards,

    Community Support Team _ Caiyun

     

Completed Ideas