Forum Discussion

pfarahani's avatar
pfarahani
Icon for Helper II rankHelper II
4 years ago

DAX measure for Average

Hello, 

I am trying to get a average for 1 row, multiple columns. Here is what I mean:

I have various Court's, and Total Filings for those courts over time, Jan 2020, Feb 2020 etc. I need to write a measure that gets the average total number of filings for each court across the entire time period that I have. Any help with this measure, thanks!

 

Need Help

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    If that is how your data is structured, you need to unpivot the Month-Year columns. 
    you can the create a measure along the lines of:

    Average Filings = CALCULATE (AVERAGE (Table[Filings]), ALLEXCEPT(Table, Table[Court]))

  • vojtechsima's avatar
    vojtechsima
    Icon for Super User rankSuper User
    4 years ago

    Hi, pfarahani ,
    I suggest doing what PaulDBrown wrote.
    But if you don'T wanna restructure your model, you can use something like his (but it's a bit awkward):

    (AVERAGE('Table'[January2021])+average('Table'[February2022])+average('Table'[March2021])))/X //X=count of columns

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Assuming you have dagged Year and Month to the column well of the matrix visual from the Calendar Table and the numbers are the result of a measure that you have written, say Measure 1, write this measure

    Measure 2 = calculate([measure 1],all(calendar[YearMonth]))

    I have assumed that YearMonth is a column in your Calendar Table.

    Hope this helps.