Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Average calculation

Hi all, I need to do average monthly calculation in pbi There was some problem with my dax, which is not giving correct average I am doing count(matter_key)  after that I am doing cumulative ...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    2 years ago

    Anonymous 

    I rewrote it to use a couple measures.

    1. Just the count of records.

     

    Matter Key Count = COUNT( 'WL Matter Extract'[MATTER_KEY] )

     

    2. A YTD running total of that count

     

    Cummulative Count = 
CALCULATE ( [Matter Key Count], DATESYTD ( 'Calendar Date'[Date] ) )

     

    3. A YTD active months count.  Only count months that have records in the 'WL Matter Extract' table

     

    Cummulative Month Count = 
CALCULATE (
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Calendar Date'[Month Year] ),
            'WL Matter Extract'
        )
    ),
    DATESYTD ( 'Calendar Date'[Date] )
)

     

    The Avg measure, where I only show the amount on months that have records to keep it from rolling forward to all future moths in the year.

     

    Avg = 
VAR _Count = [Matter Key Count]
VAR _YTDCount = [Cummulative Count]
VAR _Months = [Cummulative Month Count]
RETURN DIVIDE ( _Count, _Count ) * DIVIDE ( _YTDCount, _Months )

     

     

    I have attached my sample file for you to look at.

     

jdbuchanan71's avatar
jdbuchanan71
Super User
2 years ago

Anonymous 

Try it using AVERAGEX over your Calendar [Month Year] column. 

Average Test =
AVERAGEX (
    VALUES ( 'Calendar Date'[Month Year] ),
    CALCULATE ( COUNT ( 'WL Matter Extract'[MATTER_KEY] ) )
)

 

You need to have a month year where it is not just the month number (1, 2, 3, etc) but has the year also, Jan-2023, Feb-2023, so if you are looking at 14 months it averages 24 amounts.   If you use just 'Calendar Date'[Month Number] it would only average over 12 amounts

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi jdbuchanan71 
    Thanks alot for replying

    The below is example for output:
    I want avg for each month

    MONTH YEARMatter key countAVG
    Jan-2322
    Feb-234(4+2)/2=3
    Mar-236(2+4+6)/3=4
       
       
    Feb-9422
    Mar-944(2+4)/2=3



    Please reply back 🙂

    • Tahreem24's avatar
      Tahreem24
      Super User
      2 years ago

      Anonymous Try this solution:

      Step 1: Create a calculated column for Year

      YearColumn = YEAR(CumulativeTable[MonthYear])
       
      Step 2: Create a calculated column for Month
      MonthColumn = MONTH(CumulativeTable[MonthYear])
       
      Step 3: Create a cumulative count measure 
      CumulativeCount = CALCULATE(SUM(CumulativeTable[Matter Key]),FILTER(ALL(CumulativeTable),CumulativeTable[MonthYear]<=MAX(CumulativeTable[MonthYear]) && CumulativeTable[YearColumn]=MAX(CumulativeTable[YearColumn])))
       
      Step 4: Then create a MonthCount meaure
      MonthCount = CALCULATE(SUM(CumulativeTable[MonthColumn]),FILTER(CumulativeTable,CumulativeTable[MonthYear]<=MAX
      (CumulativeTable[MonthYear])))
       
      Step 5: And the last and final step to divide step3 and step4
      Final Answer = DIVIDE(CumulativeTable[CumulativeCount],[MonthCount])

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi,
        Thanks foe replying
        For feb there is no before month right, so avearge will be same as count might be, it should be like sum/total num of months 
        If only feb is there then 2/1
        For march feb+march/2

        Cumulative Total = CALCULATE(
            COUNT('WL Matter Extract'[MATTER_KEY]),
            FILTER (
                ALL ('Calendar Date'),
                'Calendar Date'[Date] <= MAX('Calendar Date'[Date])
            )
        )

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


        This is my exact dax for showing avearge

        Can you please let me know what changes I should do in my dax...

        Please reply back, Thanks a lot