Forum Discussion

FrankWe's avatar
FrankWe
Helper 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
Solution 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
Helper 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
    Solution 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
      Helper 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 ğŸ¤”