Forum Discussion

RichOB's avatar
RichOB
Icon for Post Partisan rankPost Partisan
1 year ago
Solved

Getting an average for the year based on Quarterly figures

Hi, how can I get the average number of clients seen per quarter based on the total seen in the fiscal year?

 

I have a COUNT measure called Client_ID

And a new column for the Fiscal_Quarter

 

How can I show that the fiscal year average is 4.25 based on the below: 

Q1 = 4

Q2 = 2

Q3 = 6

Q4 = 5

 

Table for reference:

Client_IDDate
101/04/2024
201/04/2024
301/04/2024
401/07/2024
501/07/2024
601/10/2024
701/10/2024
801/10/2024
901/10/2024
1001/10/2024
1101/10/2024
1201/01/2025
1301/01/2025
1401/01/2025
1501/01/2025
1601/01/2025

 

Thanks

Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi RichOB 

     

    Assuming that each date in your sample data is either the start or end of a fiscal quarter, try this:

    average =
AVERAGEX (
    SUMMARIZECOLUMNS ( Client[Date], "@count", COUNTROWS ( Client ) ),
    [@count]
)

     

3 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi RichOB 

     

    Assuming that each date in your sample data is either the start or end of a fiscal quarter, try this:

    average =
AVERAGEX (
    SUMMARIZECOLUMNS ( Client[Date], "@count", COUNTROWS ( Client ) ),
    [@count]
)

     

  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

    RichOB 

    Avg Clients per Quarter = 
    Var _all=CALCULATE(
    [Client_ID],
    ALL('YourTable'[Fiscal_Quarter])
    )

    Var _count=
    DISTINCTCOUNT('YourTable'[Fiscal_Quarter])

    return _all/_count

    This will give you the average number of clients seen per quarter for the fiscal year, showing 4.25 as expected based on your provided data.

    💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn