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 already repeated, so a dependent average of an....average. Makes more sense below;
| Company | Rev. Per Month | Month | rev per ticket |
| A | 1200 | 1 | ??A |
| A | 1200 | 1 | ??A |
| A | 1200 | 2 | ??A2 |
| B | 2400 | 1 | |
| B | 2400 | 1 | |
| C | 2200 | 1 | |
| C | 2200 | 1 |
In effect I'm trying to work out the average revenue per month per ticket given the above, so for ??A = 600, ??A2 = 1200 etc. The repeating rev number is throwing me off as I can't get it to divide by the count to get the rev per customer per month, before I do the rev/customer/ticket....
Hopefully that makes some sense to someone? I should add it's a simple excel upload
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
7 Replies
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 are welcome.
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
Anonymous You're welcome! DAX is definitely different to Excel in many instances, but keep at it because it is a very logical language and you'll learn to love it (and... possibly occasionally hate it haha). Let us know if you need any help or guidance if you get stuck! All the best! Theo 😃
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