{"id":3855,"date":"2022-03-15T09:50:30","date_gmt":"2022-03-15T16:50:30","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3855"},"modified":"2022-03-15T09:50:31","modified_gmt":"2022-03-15T16:50:31","slug":"how-to-use-hasonevalue-to-remove-unexpected-sum","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3855","title":{"rendered":"How to Use HASONEVALUE to Remove Unexpected Sum"},"content":{"rendered":"\n

Review the table visual, noticing the total for the Target<\/strong> column.<\/p>\n\n\n\n

\"Screenshot<\/a><\/figure>\n\n\n\n

Summing the target values together doesn\u2019t 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.<\/p>\n\n\n\n

Rename the Targets | Target column as Targets | TargetAmount. You\u2019re about to create a measure named Target<\/strong>. It\u2019s not possible to have a column and measure in the same table, with the same name.<\/p>\n\n\n\n

Create the following measure on the Targets table:<\/p>\n\n\n\n

Target =<\/p>\n\n\n\n

IF(<\/p>\n\n\n\n

HASONEVALUE(‘Salesperson (Performance)'[Salesperson]),<\/p>\n\n\n\n

SUM(Targets[TargetAmount])<\/p>\n\n\n\n

)<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The HASONEVALUE() function tests whether a single value in the Salesperson<\/strong> column is filtered. When true, the expression returns the sum of target amounts (for just that salesperson). When false, BLANK is returned.<\/p>\n\n\n\n

Add the new Target<\/strong> measure to the table visual.<\/p>\n\n\n\n

\"Screenshot<\/figure>\n\n\n\n

Add the Variance<\/strong> and Variance Margin<\/strong> measures to the table visual.<\/p>\n\n\n\n

Variance =
IF(
HASONEVALUE(‘Salesperson (Performance)'[Salesperson]),
SUM(Sales[Sales]) – [Target]
)<\/p>\n\n\n\n

Variance Margin =
Divide([Variance], [Target])<\/p>\n\n\n\n

\"Screenshot<\/figure>\n\n\n\n

Ref: Lab – Introduction to DAX in Power BI Desktop – Learn | Microsoft Docs<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Review the table visual, noticing the total for the Target column. Summing the target values together doesn\u2019t 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 Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[10,621],"tags":[1007,1008,1009],"class_list":["post-3855","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-power-bi","tag-hasonevalue","tag-remove-unexpected-sum","tag-remove-unexpected-sum-power-bi"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t