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 ...
Need Help
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.