cancel
Showing results for
Search instead for
Did you mean:
RvdHeijden Member

## Calculating the average but also in a period

I need help modifing my formula because i calculated the average number of tickets we get per week per Client with the formula below.

But when i want the average (over time) in a certain week, month, quarter it checks the clients that placed tickets in that periode and adds the averages and that is not right obviously.

I want a formula that checks the given period and calculates the number of tickets received and then gives the average.

Aantal tickets per week = round(Opdrachtgever[Aantal Tickets]/Opdrachtgever[Aantal weken];2)

I got a table where all my tickets are in (including dates and client names) and another table where my clients names are in (unique) and the total amount of tickets

1 ACCEPTED SOLUTION

Accepted Solutions v-yuezhe-msft
Moderator

## Re: Calculating the average but also in a period

Hi @RvdHeijden,

Could you please share me your PBIX file via Private Message?

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
15 REPLIES 15
Highlighted
himanshu56 Regular Visitor

## Re: Calculating the average but also in a period

You can use the CALCULATE DAX in this particular case.

CALCULATE( <expression>, <filter1>, <filter2>… )

CALCULATE( COUNT(NoOfTickets),
ALL( 'Table'[Name] ),
TableName[Period] = "Certain week/month.quarter" )

Please have a look at the following blog where it is very clearly explained about CALCULATE DAX

http://sqlblog.com/blogs/marco_russo/archive/2010/01/03/how-calculate-works-in-dax.aspx

Thanks,

Himanshu

RvdHeijden Member

## Re: Calculating the average but also in a period

@himanshu56

What should be used in the last part of your formula, because this depends on what i use the 'Timeline' visual with the 'startdate' of the ticket

CALCULATE( COUNT(NoOfTickets),
ALL( 'Table'[Name] ),
TableName[Period] = "Certain week/month.quarter" )

EDIT:

I've read the blog but that only uses Calculate in a Measurement and im not quite sure on how to use it in this case.

himanshu56 Regular Visitor

## Re: Calculating the average but also in a period

CALCULATE (
COUNT( TableName[No of tickets] ),
FILTER (
ALL ( 'DateTable'[Date] ),
'DateTable'[Date] <= MAX ( 'DateTable'[Date] )  // This particular line will return the count of all the tickets for those days which are less than what you have selected in your timeline visual
)
)

CALCULATE (
COUNT( TableName[No of tickets] ),
FILTER (
ALL ( 'DateTable'[Date] ),
'DateTable'[Date] = MONTH([DateTable[Date]])  // This particular line will return the count of all the tickets for those months which are less than what you have selected in your timeline visual
)
)

In the same way you can play around with Time Intelligence DAX available in Power BI.

Thanks,
Himanshu
RvdHeijden Member

## Re: Calculating the average but also in a period

@himanshu56

Thanks for your help but Im sorry but your formula doesn't produce the result i wanted.

If i select 10 weeks it says i have 210 tickets, then it should read 210/10=21 tickets and now it reads 12 tickets

Even if i select 1 week the total amount of tickets that week was 24 and the average should be the same, 24 again but now it reads 5

Average = CALCULATE (
COUNT(Opdrachtgever[Aantal Tickets] );
FILTER (ALL (Tickets[Startdatum]);
Tickets[Startdatum] <= MAX ( ( Tickets[Startdatum] )))) v-yuezhe-msft
Moderator

## Re: Calculating the average but also in a period

Hi @RvdHeijden,

Could you please share sample data of your tables and post expected result here?

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RvdHeijden Member

## Re: Calculating the average but also in a period

@v-yuezhe-msft

The data is in one table and that table contains al of our tickets with ticketinfo such as client name, start- and enddate.

What i expect is that when i use the timeline visual and select for example only january of 2017 it reads 100 tickets and the average is 100/4 (weeks)= 25 tickets per week

When i selecte Q1 of 2017 it should read 150 tickets but it should also calculate the average number of tickets that periode.

If i select Q1 in my timeline visual it should check the earliest date in my selection and check my last date in my selection and then calculate the number of weeks in between these 2 dates and calculate:

[number of tickets with a startdate in the given period]   /   [number of weeks in the given period] = average

When i want an average per week i need a column right ? but when i want an average per month, quarter and or year do i need 4 extra colums ? v-yuezhe-msft
Moderator

## Re: Calculating the average but also in a period

Hi @RvdHeijden,

You should have a calendar table contains Date column which is used by Timeline slicer, then create following columns(StartDate and EndDate ) and DateDiff measure in calendar table.

StartDate = CALCULATE ( MIN ( 'Calendar'[DateKey]), ALLSELECTED ( 'Calendar'[DateKey] ) )

EndDate = CALCULATE ( MAX('Calendar'[DateKey]), ALLSELECTED ( 'Calendar'[DateKey] ) )

number of weeks in the given period =
CALCULATE (
DATEDIFF ( MIN('Calendar'[DateKey]), MAX ('Calendar'[DateKey] ), WEEK),
ALLSELECTED ( 'Calendar'[DateKey] )
)

Then create a measure in your fact table to calculate count of tickets.(In my example, I calculate sum of salesamount, in your scenario, the DAX should be: number of tickets= COUNT(Opdrachtgever[Aantal Tickets]))

Selectedamount = SUM(Sales[SalesAmount])

At last, create a measure using the DAX below.( in your scenario, the DAX should be: Average = [number of tickets]/[number of weeks in the given period])

Average = [Selectedamount]/[number of weeks in the given period] Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RvdHeijden Member

## Re: Calculating the average but also in a period

@v-yuezhe-msft

Thanks Lydia for your help and i feel we are close to a solution.

I made a new table using the CALENDAR function and added 2 new colums but both formulas 'Startdate' and 'Enddate' return an error

StartDate = CALCULATE ( MIN ( Date[Date]); ALLSELECTED ('Date'[Date]))

The syntax for '[Date]' is incorrect. (DAX(CALCULATE ( MIN ( Date[Date]), ALLSELECTED ('Date'[Date])))).

EndDate = CALCULATE ( MAX('Date'[Date]); ALLSELECTED ( 'Date'[Date]) )

A circular dependency was detected: Date[Column], 6c5bdbee-a10d-4dfb-9fb0-b10d4e625be5, Date[Column].

Both measurements 'Number of Weeks in given period' and 'Average' dont return an error v-yuezhe-msft
Moderator

## Re: Calculating the average but also in a period

Hi @RvdHeijden,

Do you right click your calendar table and choose "New column" to apply DAX of StartDate and  EndDate? Check the following DAX. Also what is the Date[Column] in your table? Please remove this column and then create EndDate column.

StartDate = CALCULATE ( MIN ( 'Date'[Date] ); ALLSELECTED ( 'Date'[Date] ) ) Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

## Helpful resources

Announcements Top Ideas Top Kudoed Authors
Users Online
Currently online: 236 members 2,504 guests
Recent signins:
• dmotter • ameyabhave • Bi890_ • MiguelGA • armindom • vyuvaraj • lashkarwala Please welcome our newest community members:
• lashkarwala • chaynie • drewference • baloo • pablobarajas • HaughnD • matt_wil 