Forum Discussion
Data manipulation, create average across multiple entries
Hi, Long time lurker, first time caller...and I've searched but can't figure it out... I have a data set pretty much as below and I'm trying to get an average where the field I'm averaging is a...
Hi Anonymous
You can use the following measure to achieve what you're after:
Avg by Group = CALCULATE ( AVERAGE ( 'Table'[Revenue] ) , ALLEXCEPT ( 'Table' ,'Table'[Company] ,'Table'[Month] ) )
Output of an example is below with attached PBIX file:
In the example above, I used Category and Year. You can change these as you require by adjusting the measure.
Hope this helps!
Theo
Hi,
This calculated column formula works
=DIVIDE(Data[Rev. Per Month],CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Company]=EARLIER(Data[Company])&&Data[Month]=EARLIER(Data[Month]))))Hope this helps.
You can use the following measure to achieve what you're after:
Avg by Group = CALCULATE ( AVERAGE ( 'Table'[Revenue] ) , ALLEXCEPT ( 'Table' ,'Table'[Company] ,'Table'[Month] ) )
Output of an example is below with attached PBIX file:
In the example above, I used Category and Year. You can change these as you require by adjusting the measure.
Hope this helps!
Theo
Hi,
This calculated column formula works
=DIVIDE(Data[Rev. Per Month],CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Company]=EARLIER(Data[Company])&&Data[Month]=EARLIER(Data[Month]))))Hope this helps.
Thanks Ashish, looks like multiple ways to do the same thing and on my own I failed to find any of them!!
You are welcome.