cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RvdHeijden Member
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
Moderator 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.

View solution in original post

15 REPLIES 15
himanshu56 Regular Visitor
Regular Visitor

Re: Calculating the average but also in a period

Hi @RvdHeijden

 

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
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
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
Highlighted
RvdHeijden Member
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] ))))

 

 

Moderator 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
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 ?

Moderator 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]

1.PNG

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

 

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

1.PNG


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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 60 members 1,119 guests
Please welcome our newest community members: