Imagine you want to compare budget VS actuals but the budget is incomplete. There is a limited amount of time, so completing the budget manually is not an option. We’ll use actuals wherever budget amounts are missing.
So far so good. But wait a second… what is that? The Power BI Measure Totals are incorrect …!
Recently I came across this scenario and I wanted to share a possible solution for this problem. The detailed scenario can be found in the blog post below.
Scenario: a call center that budgetted a certain amount of minutes for every line, but did NOT budget a certain amount of calls for every line. The goal of management is to compare the average handling time (defined as TotalMinutes / TotalCalls) with the budgetted handling time, but that proves difficult with a budget that is incomplete. There is no time to enter new values for the budgetted total calls because management wants to analyze right NOW. They tell the resident Data Analyst to the use real total calls instead of the budgetted total calls whenever the budgetted total calls is missing. Time to roll up our sleeves…
AvgHandlingTimeBudget NEW CorrectTotal =
// Scenario: a call center that budgetted a certain amount of minutes for every line, but did NOT budget a certain amount of calls for every line. The goal of management is to compare the average handling time (defined as TotalMinutes / TotalCalls) with the budgetted handling time, but that proves difficult with a budget that is incomplete. There is no time to enter new values for the budgetted total calls because management wants to analyze right NOW. They tell the resident Data Analyst to the use real total calls instead of the budgetted total calls whenever the budgetted total calls is missing. Time to roll up our sleeves...
VAR _ResultRows =
SWITCH(
TRUE(),
ISBLANK(SUM('Table'[TotalCallsBudget])), 550, // If total calls budget is empty, then use 550 calls as the budgetted amount of calls for this line
[AvgHandlingTimeBudget] // If total calls budget is NOT empty, then divide the budgetted total minutes by the budgetted total minutes
) // Done! ... Or are we? The amount on the total row is incorrect! Greg Deckler grumbling intensifies!
VAR _TableTotal = // Create a table in order to calculate a new budgetted total calls amount
ADDCOLUMNS(
'Table',
"TotalCallsBudgetNEW", DIVIDE('Table'[TotalMinutesBudget], [AvgHandlingTimeBudget NEW WrongTotal]) // We can get the total amount of calls by dividing the total minutes by the new average handling time
)
VAR _ResultTotalRow =
DIVIDE(
SUMX(_TableTotal, [TotalMinutesBudget]), // The total amount of budgetted minutes were already present so we use this as the numerator
SUMX(_TableTotal, [TotalCallsBudgetNEW]) // Our newly created budgetted total calls is here to act as the denominator
)
VAR _Result = // All set.
IF(
ISINSCOPE('Table'[Line]), // If if it not the total row...
_ResultRows, // ... Use the results from the variable _ResultRows
_ResultTotalRow // ... Otherwise use the results from the variable _ResultTotalRow!
)
RETURN
_Result