Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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       ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    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.

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
6 years ago

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))