Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    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

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

  • TheoC's avatar
    TheoC
    4 years ago

     

    Hi @ChiefOfNothing 

     

    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

TheoC's avatar
TheoC
Community Champion
4 years ago

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