Forum Discussion
Calculating average from multiple columns based on index value
Hi! I'm trying to figure out a way to calculate average time spent in each status from the 'Ticket Table' and have it stored in a seperate 'Target vs Average' table. I have screenshots of example data below. This was easy to do with formulas in Excel in the YELLOW columns, but I need to get rid of these yellow/formula-calculated columns have have the calculations done in Power BI instead. The PURPLE columns are the data that I have in Power BI in 2 separate tables, 'Ticket Table' and 'Target vs Average'.
A little more background on the data: the system I'm exporting the data from records time stamps for when a ticket changes statuses. I have metrics/goals around how long tickets should stay in each status, so the end goal is to compare how long on average tickets are staying in each status compared to the target time I want them to stay in each status. An example with this data is "the average time spent in the Implementation status is 7 network days, which is higher than the target of 5 network days". The tickets move statuses linearly, New > Implementation > Approval > Completed.
I can't figure out how to recreate the yellow columns in Power BI, mainly because the Average Days in Status column references different columns in the Ticket Table. Ex. - The Average Days in Status for 'New' (Target vs Average C4) is calculated based on column G/'Days in New' in the Ticket Table, and the Average Days in Status for 'Approval' (Target vs Average C5) is calculated based on column H/'Days in Approval'.
Does anyone know a way to do these yellow calculations in Power BI instead of Excel??? I would GREATLY appreciate any help
Hi mlpoole85 -calculate the average time spent in each status in Power BI and compare it with target times
In power bi use the calculated column (DAX) for Days in Each Status
DaysInStatus =
SWITCH(
TRUE(),
'Ticket Table'[Status] = "New",
DATEDIFF('Ticket Table'[New_Status_Date], 'Ticket Table'[Implementation_Status_Date], DAY),
'Ticket Table'[Status] = "Implementation",
DATEDIFF('Ticket Table'[Implementation_Status_Date], 'Ticket Table'[Approval_Status_Date], DAY),
'Ticket Table'[Status] = "Approval",
DATEDIFF('Ticket Table'[Approval_Status_Date], 'Ticket Table'[Completed_Status_Date], DAY),
BLANK()
)Now let's create a measure to get the average Days in Status
AvgDaysInStatus =
VAR CurrentStatus = SELECTEDVALUE('Ticket Table'[Status])
RETURN
SWITCH(
CurrentStatus,
"New",
CALCULATE(
AVERAGE('Ticket Table'[DaysInStatus]),
'Ticket Table'[Status] = "New"
),
"Implementation",
CALCULATE(
AVERAGE('Ticket Table'[DaysInStatus]),
'Ticket Table'[Status] = "Implementation"
),
"Approval",
CALCULATE(
AVERAGE('Ticket Table'[DaysInStatus]),
'Ticket Table'[Status] = "Approval"
),
BLANK()
)Hope it helps
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
1 Reply
Hi mlpoole85 -calculate the average time spent in each status in Power BI and compare it with target times
In power bi use the calculated column (DAX) for Days in Each Status
DaysInStatus =
SWITCH(
TRUE(),
'Ticket Table'[Status] = "New",
DATEDIFF('Ticket Table'[New_Status_Date], 'Ticket Table'[Implementation_Status_Date], DAY),
'Ticket Table'[Status] = "Implementation",
DATEDIFF('Ticket Table'[Implementation_Status_Date], 'Ticket Table'[Approval_Status_Date], DAY),
'Ticket Table'[Status] = "Approval",
DATEDIFF('Ticket Table'[Approval_Status_Date], 'Ticket Table'[Completed_Status_Date], DAY),
BLANK()
)Now let's create a measure to get the average Days in Status
AvgDaysInStatus =
VAR CurrentStatus = SELECTEDVALUE('Ticket Table'[Status])
RETURN
SWITCH(
CurrentStatus,
"New",
CALCULATE(
AVERAGE('Ticket Table'[DaysInStatus]),
'Ticket Table'[Status] = "New"
),
"Implementation",
CALCULATE(
AVERAGE('Ticket Table'[DaysInStatus]),
'Ticket Table'[Status] = "Implementation"
),
"Approval",
CALCULATE(
AVERAGE('Ticket Table'[DaysInStatus]),
'Ticket Table'[Status] = "Approval"
),
BLANK()
)Hope it helps
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!