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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 SiteDestinationPoll Date RTD2 Month Average RTD
DHAKAOSAKA-R-0131/07/201979.7751.3
DHAKA OSAKA-R-0131/08/201922.8251.3
DHAKA OSAKA-R-0130/09/2019122.2151.3
DHAKA OSAKA-R-0231/03/2020256.12257.11
DHAKA OSAKA-R-0230/04/2020258.09257.11
DHAKA OSAKA-R-0231/05/2020266.26257.11
DHAKA SINGAPORE-R-0131/07/201933.3721.45
DHAKA SINGAPORE-R-0131/08/20199.5321.45
DHAKA SINGAPORE-R-0130/09/201951.1221.45

 

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

 

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    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 

     

    You can add another column in the table.

     

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