Average by Multiple Categories

[Anonymous]

Hello Power BI Community!

I would like some help calculating the average RTD using only the first two months of Poll data for each combination of Source Site and Destination.

Example                                                                                                                           

ARC-BD-DHAKA-01-R-01 ➡ ARC-JP-OSAKA-01-R-01            ( 79.77 + 22.82 ) / 2    = 51.30

ARC-BD-DHAKA-01-R-01 ➡ ARC-JP-OSAKA-01-R-02            ( 256.12+258.09 ) / 2  = 257.11

ARC-BD-DHAKA-01-R-01 ➡ ARC-SG-SINGAPORE-01-R-01   ( 33.37 +9.53 ) / 2       =  21.45

Ideally the average RTD would be in the form of a column rather a measure, as this figure will be displayed in the row of the below matrix.

Thank you for your help 😄 

@AllisonKennedy , @Greg_Deckler , @amitchandak , @Ashish_Mathur 

[Anonymous]

Hi Power Bi Community !

I require the solution to be in the form of a calculated column not a measure.

This what I am looking for.

Example Table

Source Site	Destination	Poll Date	RTD	2 Month Average RTD
DHAKA	OSAKA-R-01	31/07/2019	79.77	51.3
DHAKA	OSAKA-R-01	31/08/2019	22.82	51.3
DHAKA	OSAKA-R-01	30/09/2019	122.21	51.3
DHAKA	OSAKA-R-02	31/03/2020	256.12	257.11
DHAKA	OSAKA-R-02	30/04/2020	258.09	257.11
DHAKA	OSAKA-R-02	31/05/2020	266.26	257.11
DHAKA	SINGAPORE-R-01	31/07/2019	33.37	21.45
DHAKA	SINGAPORE-R-01	31/08/2019	9.53	21.45
DHAKA	SINGAPORE-R-01	30/09/2019	51.12	21.45

Thanks  @ryan_mayu your measures work well. I just require a different format.

[ryan_mayu]

@Anonymous 

You can add another column in the table.

Column = 
AVERAGEX(FILTER(Sheet12,Sheet12[destination]=EARLIER(Sheet12[destination])&&Sheet12[Column2]<=2),Sheet12[RTD])

[Ashish_Mathur · Accepted solution]

Hi,

These calculated column formulas work

First poll data for source site and destination combination = CALCULATE(MIN(Data[Poll Date]),FILTER(Data,Data[Source Site]=EARLIER(Data[Source Site])&&Data[Destination]=EARLIER(Data[Destination])))

2 month average RTD = CALCULATE(AVERAGE(Data[RTD]),FILTER(Data,Data[Source Site]=EARLIER(Data[Source Site])&&Data[Destination]=EARLIER(Data[Destination])&&EDATE(Data[First poll date for Source site and destination combination],1)>=Data[Poll Date]))

Hope this helps.

[mahoneypat]

Here is another approach to do this as either a column or a measure

Measure - 

Avg RDT First Two Months =
CALCULATE (
AVERAGE ( RTD[RTD] ),
TOPN ( 2, VALUES ( RTD[Poll Date] ), RTD[Poll Date], ASC )
)

Calculated Column - 

Avg RDT First Two Months =
CALCULATE (
AVERAGE ( RTD[RTD] ),
ALLEXCEPT ( RTD, RTD[Source Site], RTD[Destination] ),
TOPN ( 2, VALUES ( RTD[Poll Date] ), RTD[Poll Date], ASC )
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[ryan_mayu]

@Anonymous 

Create a rank column and rank by destination.

rank = RANKX(FILTER(Sheet12,Sheet12[destination]=earlier(Sheet12[destination])),Sheet12[Polldate],,ASC)

Then create a measure calculate the average value based on the ranking.

Measure = CALCULATE(AVERAGE(Sheet12[RTD]),FILTER(Sheet12,Sheet12[rank]<=2))

[Greg_Deckler]

@Anonymous - You should be able to get there using Lookup Min/Max https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

If that doesn't get you there, please post your data as text in a table. Thanks.