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 total for this and doing avearge using month number
Below is my dax

Cumulative Total =

CALCULATE(COUNT('WL Matter Extract'[MATTER_KEY]),

    FILTER (

        ALL ('Calendar Date'[Date]),

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

    )

)

 


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

I am facing issue when I have only one month dataa in a year
For example, I have Only feb month data in 2001 year then average for that year should be same as the count of matter key, 
But with my dax it's dividing with feb month number and giving avearge

 

Please help 🙂
Thanks in advance



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.

     

6 Replies

  • 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])

         

         

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    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.