Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi @RvdHeijden,
Could you please share me your PBIX file via Private Message?
Thanks,
Lydia Zhang
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
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.
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] ))))
Hi @RvdHeijden,
Could you please share sample data of your tables and post expected result here?
Thanks,
Lydia Zhang
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 ?
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
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
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
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].
@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
@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 ?
Hi @RvdHeijden,
Yes. Relationship should exist in Ticket table and Date table.
Thanks,
Lydia Zhang
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)
Hi @RvdHeijden,
Could you please share me your PBIX file via Private Message?
Thanks,
Lydia Zhang
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |