Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Average calculation

Hi I can't seem to figure out how to calculate this. I have the following table. the automatic AVG measure  by powerbi is not what i want as it averages the single transaction appening in a month. I want to know the average for the year.
ideally that would be:

(total amount of the year)/(count of month in the year)

but I cannot create the correct measure.

 

 

Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous This should work in something like a card visual:

    Measure = DIVIDE( SUM('Table'[Amount]), 12 )

    Or, if you want it to be cumulative you could do this:

    Measure =
  VAR __Date = MAX('Table'[Date])
  VAR __Table = 
    SUMMARIZE(
      FILTER( 'Table', [Date] <= __Date ),
      [Month],
      "__Amount", SUM( 'Table'[Amount] )
    )
  VAR __Result = DIVIDE( SUMX( __Table, [__Amount] ), COUNTROWS( __Table ) )
RETURN
  __Result
    • Simon3's avatar
      Simon3
      Frequent Visitor
      3 years ago

      Hi Greg_Deckler thanks for the reply I actually used a different method but I still need a bit of help.
      Let me specify more things about my data set.
      I have transactions (categorized and sub-categorized) spread across different years. I want to achieve two things: 
      1. to have the AVG amount of the transaction sums per each year 
      2. the same as above but divided by categories
      So far I have been able to create static tables and measures filtering what I want with the two following formulas:
      Table = SUMMARIZE(
      FILTER(Merged_new,[Year]=2022 && [Type]="Expenses"),
      Merged_new[Year],
      Merged_new[Month],
      Merged_new[Type],
      "Sum_Net_Amount",SUM(Merged_new[Net Amount])
      )

      Measure =
      AVERAGEX(
      ALL('Table'[Month]),
      CALCULATE(SUM('Table'[Sum_Net_Amount]))
      )


      This is very tedious and manual work obviously. How could I improve this and make it more dynamic? Any input will be highly appreciated.
      If needed I can also provide the pwbi file.