Forum Discussion
Line Graph with 10 Minute Intervals on x-axis
Create another calculated column
Setup Time2 = DATE ( YEAR ( 'BI Folder'[Setup Time] ), MONTH ( 'BI Folder'[Setup Time] ), DAY ( 'BI Folder'[Setup Time] ) ) + TIME ( HOUR ( 'BI Folder'[Setup Time] ), FLOOR ( MINUTE ( 'BI Folder'[Setup Time] ), 10 ), 0 )And an extra measure
ASR in last 10 mins = CALCULATE(DIVIDE (SUM ('BI Folder'[Answer Status]), COUNTA('BI Folder'[Session Status])),LASTNONBLANK('BI Folder'[Setup Time2],""))If it answers your question, please accept it as answer. For any question, feel free to let me know.
Thanks for taking out time to help. Let me try my best to explain what I want to achieve with Power BI more clearly. The link below is a sample file I created from the actual dataset to quickly recreate the problem.
https://drive.google.com/file/d/0Bz0yHmC0QyGYYVNhZ1RickN0MVU/view?usp=drivesdk
There are three columns. 'Setup Time', 'Session Status' & 'Session Duration'
A 'Session Status' of 3 means that the call was answered.
A 'Session Status' of 2 means that the callee's phone rang but no pickup.
A 'Session Status' of 1 means that the call didn't get to the callee's phone or it was switched off.
The KPI I'm interested in at the moment is ASR.
ASR means Answer Seizure Ratio in percentage. So this is how I intend dealing with it;
1. I know that every ROW of data represents one session / call, so a row count should give me number of calls.
2. So I created a calculated column called 'Answer Status'
Answer Status = IF('BI Folder'[Session Status] = 3, 1, 0)
3. I then created a measure called ASR as follows
ASR = DIVIDE (SUM ('BI Folder'[Session Status]), COUNTA(BI Folder'[Session Status]))
I want to get the ASR at every 10 minutes interval (and it doesn't have to be on a line graph, I could use a card that updates with the latest 10 minute interval.
Instead Power BI will give me ASR by aggregations of Year, Month or Day. I want it in 10 minutes interval instead. Please how can I achieve this?
Power BI unaggregated to the minutes
Microsoft Employee
Create another calculated column
Setup Time2 =
DATE ( YEAR ( 'BI Folder'[Setup Time] ), MONTH ( 'BI Folder'[Setup Time] ), DAY ( 'BI Folder'[Setup Time] ) )
+ TIME ( HOUR ( 'BI Folder'[Setup Time] ), FLOOR ( MINUTE ( 'BI Folder'[Setup Time] ), 10 ), 0 )
And an extra measure
ASR in last 10 mins = CALCULATE(DIVIDE (SUM ('BI Folder'[Answer Status]), COUNTA('BI Folder'[Session Status])),LASTNONBLANK('BI Folder'[Setup Time2],""))
If it answers your question, please accept it as answer. For any question, feel free to let me know.