Forum Discussion
Average by Multiple Categories
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
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.
6 Replies
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.
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))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.
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
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.
Anonymous
You can add another column in the table.
Column = AVERAGEX(FILTER(Sheet12,Sheet12[destination]=EARLIER(Sheet12[destination])&&Sheet12[Column2]<=2),Sheet12[RTD])
