Calculating the average but also in a period

[RvdHeijden]

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

[himanshu56]

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]

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

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]

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

Hi @RvdHeijden,

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

Thanks,
Lydia Zhang

[RvdHeijden]

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

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

[RvdHeijden]

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

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

[RvdHeijden]

ive got the 'StartDate' working but the EndDate keeps returning an error

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

Error:         A circular dependency was detected: Date[StartDate], Date[EndDate], Date[StartDate].

[v-yuezhe-msft]

@RvdHeijden,

Create measures instead of columns using the following DAX in your date table.

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

Actually, you don't need to create StartDate and EndDate in your scenario, I just want to show you the number of weeks between two dates when selecting date range in Timeline.

Thanks,
Lydia Zhang

[RvdHeijden]

@v-yuezhe-msft Shouldn't there be a relationship between the 'Date' table and the 'Ticket' table ?

Otherwise it wont SUM the number of tickets entered in a given week right ?

[v-yuezhe-msft]

Hi @RvdHeijden,

Yes. Relationship should exist in Ticket table and Date table.

Thanks,
Lydia Zhang

[RvdHeijden]

@v-yuezhe-msft

Between what 2 colums should the relationship be ? i tried matching dates (one to many) between the Date table and the Ticket table but that doesn't work.

The Timeline visual doesnt even work if i have a relationship between Date (in table 'Date') and Startdatum (in table 'Tickets)

[v-yuezhe-msft · Accepted solution]

Hi @RvdHeijden,

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

Thanks,
Lydia Zhang