Data manipulation, create average across multiple entries

[ChiefOfNothing]

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

[TheoC · Accepted solution]

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

[Ashish_Mathur · Accepted solution]

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 · Accepted solution]

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

[ChiefOfNothing]

Thank you! Now to manipulate that into what I want as an end goal, I'll try and work that one out myself. I'm normally pretty solid on excel, but this feels like a new language!  Thank you!

[TheoC]

@ChiefOfNothing 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 😃 

[Ashish_Mathur · Accepted solution]

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.

[ChiefOfNothing]

Thanks Ashish, looks like multiple ways to do the same thing and on my own I failed to find any of them!!

[Ashish_Mathur]

You are welcome.

[TheoC · Accepted solution]

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