Forum Discussion

FrankWe's avatar
FrankWe
Icon for Helper II rankHelper II
2 years ago
Solved

Problem with DAX query calculating an average value

Hello,  I have a problem to create a DAX measure calculating an average value.  I have a combined bar and line chart. The bars are showing the total numbers of rows in a table "visits" per month on...
  • sjoerdvn's avatar
    sjoerdvn
    2 years ago

    since you're not using a measure, you have to add a calculate to force the context.

    VAR AverageVisits2 = 
CALCULATE(
    AVERAGEX(
        VALUES('Calendar'[MonthYear]),
        CALCULATE(COUNT(Visits[VisitID])))
   ,ALLSELECTED('Calendar')
)
sjoerdvn's avatar
sjoerdvn
Icon for Solution Sage rankSolution Sage
2 years ago

I have created something similar, but you should be able to adjust to your own measures and date dimension table. crucial  is the use of ALLSELECTED to give you the filter/slicer context:

avg montly visits = CALCULATE(AVERAGEX(VALUES('dim_date'[month_year]),[# visits]), ALLSELECTED('dim_date'))



  • FrankWe's avatar
    FrankWe
    Icon for Helper II rankHelper II
    2 years ago

    sjoerdvn: Hi, thank your this. I tried to implement this, but the result is that the line is showing me the total number of visits in the selected time period and not the average ğŸ¤”

     

    My measure looks like this now:

     

    VAR AverageVisits2 = CALCULATE(
        AVERAGEX(
            VALUES('Calendar'[MonthYear]),
            COUNT(Visits[VisitID])),
            ALLSELECTED('Calendar')
        )
     
    What is wrong whit this? 
    • sjoerdvn's avatar
      sjoerdvn
      Icon for Solution Sage rankSolution Sage
      2 years ago

      since you're not using a measure, you have to add a calculate to force the context.

      VAR AverageVisits2 = 
CALCULATE(
    AVERAGEX(
        VALUES('Calendar'[MonthYear]),
        CALCULATE(COUNT(Visits[VisitID])))
   ,ALLSELECTED('Calendar')
)
      • FrankWe's avatar
        FrankWe
        Icon for Helper II rankHelper II
        2 years ago

        @sjoerdvn: Many thanks for your help. You are my hero ğŸ˜Š And it shows me that I will have to learn so much more to understand more of the DAX functionality ğŸ¤”