{"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<p>Review the table visual, noticing the total for the&nbsp;<strong>Target<\/strong>&nbsp;column.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><a href=\"https:\/\/docs.microsoft.com\/en-us\/learn\/modules\/create-measures-dax-power-bi\/media\/lab-6-37-ssm.png#lightbox\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/learn\/modules\/create-measures-dax-power-bi\/media\/lab-6-37-ssm.png\" alt=\"Screenshot that highlights the Target total.\"\/><\/a><\/figure>\n\n\n\n<p>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<p>Rename the Targets | Target column as Targets | TargetAmount. You\u2019re about to create a measure named&nbsp;<strong>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<p>Create the following measure on the Targets table:<\/p>\n\n\n\n<p>Target =<\/p>\n\n\n\n<p>IF(<\/p>\n\n\n\n<p>HASONEVALUE(&#8216;Salesperson (Performance)'[Salesperson]),<\/p>\n\n\n\n<p>SUM(Targets[TargetAmount])<\/p>\n\n\n\n<p>)<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img fetchpriority=\"high\" decoding=\"async\" width=\"506\" height=\"213\" src=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2021\/12\/image.png\" alt=\"\" class=\"wp-image-3856\" srcset=\"https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2021\/12\/image.png 506w, https:\/\/SUMMALAI.COM\/wp-content\/uploads\/2021\/12\/image-300x126.png 300w\" sizes=\"(max-width: 506px) 100vw, 506px\" \/><\/figure>\n\n\n\n<p>The HASONEVALUE() function tests whether a single value in the&nbsp;<strong>Salesperson<\/strong>&nbsp;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<p>Add the new&nbsp;<strong>Target<\/strong>&nbsp;measure to the table visual.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/learn\/modules\/create-measures-dax-power-bi\/media\/lab-6-39-ssm.png\" alt=\"Screenshot that verifies the Target total is blank.\"\/><\/figure>\n\n\n\n<p>Add the&nbsp;<strong>Variance<\/strong>&nbsp;and&nbsp;<strong>Variance Margin<\/strong>&nbsp;measures to the table visual.<\/p>\n\n\n\n<p>Variance =<br>IF(<br>HASONEVALUE(&#8216;Salesperson (Performance)'[Salesperson]),<br>SUM(Sales[Sales]) &#8211; [Target]<br>)<\/p>\n\n\n\n<p>Variance Margin =<br>Divide([Variance], [Target])<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/docs.microsoft.com\/en-us\/learn\/modules\/create-measures-dax-power-bi\/media\/lab-6-40-ss.png\" alt=\"Screenshot of all values displayed.\"\/><\/figure>\n\n\n\n<p>Ref: <a href=\"https:\/\/docs.microsoft.com\/en-us\/learn\/modules\/create-measures-dax-power-bi\/6-lab\">Lab &#8211; Introduction to DAX in Power BI Desktop &#8211; Learn | Microsoft Docs<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Review the table visual, noticing the total for the&nbsp;Target&nbsp;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 <a class=\"read-more\" href=\"https:\/\/SUMMALAI.COM\/?p=3855\">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,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"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":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3855","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3855"}],"version-history":[{"count":3,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3855\/revisions"}],"predecessor-version":[{"id":4079,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3855\/revisions\/4079"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3855"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3855"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3855"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}