Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

12 months average

Hi all,   I have req as doing average for each month  Before I was showing count (I'd) for each date, mknth, year Now I want to show average like In Jan if I have count of id 700 then avg will b...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,
I tried below dax 

I took

 

Cumulative Total =

CALCULATE(COUNT('MATTER TABLE'[ID]),

    FILTER (

        ALL ('Calendar Date'[Date]),

        'Calendar Date'[Date] <= MAX ( ('Calendar Date'[Date] ) )

    )

)

 

Average Test = DIVIDE([Cumulative Total],MAX('Calendar Date'[Month Sort]),0)

 

Firstly I took cumulative total cases, and after that i did average of it by dividing total cumulative with month number for respective months

I am doing it to get average of open matters per month in a particular year
The dax I used is showing correct count when I am selecting a particular year in open date
But i want it to show the correct average in trends manner for all years with correct average at a time not only for single year

Please Help

Thanks in advance

Dangar332's avatar
Dangar332
Resident Rockstar
2 years ago

Hi, Anonymous 

try below

Cumulative Total =

CALCULATE(COUNT('MATTER TABLE'[ID]),

    FILTER (

        ALL ('Calendar Date'[Date]),

        'Calendar Date'[Date] <= MAX ('Calendar Date'[Date] ) && 
        'Calendar Date'[year] = year(MAX ('Calendar Date'[Date] ))

    )

)

 

Average Test = DIVIDE([Cumulative Total],MAX('Calendar Date'[Month Sort]),0)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

    it's giving below error 

    A single value for column 'YEAR' in table 'Calendar Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

    • Dangar332's avatar
      Dangar332
      Resident Rockstar
      2 years ago

      Hi, Anonymous 

      update code of all('calender date')

       

      Cumulative Total =

CALCULATE(COUNT('MATTER TABLE'[ID]),

    FILTER (

        ALL ('Calendar Date'),

        'Calendar Date'[Date] <= MAX ('Calendar Date'[Date] ) && 
        'Calendar Date'[year] = year(MAX ('Calendar Date'[Date] ))

    )

)

 

Average Test = DIVIDE([Cumulative Total],MAX('Calendar Date'[Month Sort]),0)

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Thanks for replying

        calculation error in measure [ Cumulative Total] :dax comparision operation do not support comparing values of type text with values of type integer. Consider using VALUE or FORMAT Function

        I tried foemat and value also, shoiwng below error

        A single value for column 'YEAR' in table 'Calendar Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
        Please reply back

        Thanks