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 ...
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.
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
Super User
Super UserAnonymous
You can add another column in the table.
Column =
AVERAGEX(FILTER(Sheet12,Sheet12[destination]=EARLIER(Sheet12[destination])&&Sheet12[Column2]<=2),Sheet12[RTD])