Review the table visual, noticing the total for the Target column.
Summing the target values together doesn’t make sense because salespeople targets are set for each salesperson based on their sales region assignment(s). A target value should only be shown when a single salesperson is filtered. You will implement a measure now to do just that.
Rename the Targets | Target column as Targets | TargetAmount. You’re about to create a measure named Target. It’s not possible to have a column and measure in the same table, with the same name.
Create the following measure on the Targets table:
Target =
IF(
HASONEVALUE(‘Salesperson (Performance)'[Salesperson]),
SUM(Targets[TargetAmount])
)
The HASONEVALUE() function tests whether a single value in the Salesperson column is filtered. When true, the expression returns the sum of target amounts (for just that salesperson). When false, BLANK is returned.
Add the new Target measure to the table visual.
Add the Variance and Variance Margin measures to the table visual.
Variance =
IF(
HASONEVALUE(‘Salesperson (Performance)'[Salesperson]),
SUM(Sales[Sales]) – [Target]
)
Variance Margin =
Divide([Variance], [Target])
Ref: Lab – Introduction to DAX in Power BI Desktop – Learn | Microsoft Docs