{"id":1416,"date":"2024-06-11T12:42:12","date_gmt":"2024-06-11T10:42:12","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=1416"},"modified":"2024-06-11T12:42:12","modified_gmt":"2024-06-11T10:42:12","slug":"power-bi-create-a-top-n-measure","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=1416","title":{"rendered":"Power BI, Create a TOP n Measure"},"content":{"rendered":"\n

The situation is relatively simple. I needed a Power BI measure where I could see the top three companies with the highest sales, depending on the filters set. A colleague told me that it would be relatively simple to use a filter to only show the top three and sort the results in descending order of sales. That’s true in principle, but the problem is that if further filters are set, second place may be eliminated and the order changes. The example model is limited to one table for the sake of simplicity.<\/p>\n\n\n\n

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

Here you can add up and display the sales by category. The top three categories would be C, B and A. Category D would be in fourth place. If you now create a table in which only the top three sales are displayed by category but filter out category C, then you will also see category D in the result. In principle this is correct, but it may not be the expected result. In my case, only categories A and B should be displayed.<\/p>\n\n\n\n

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

However, if the visualization is not filtered, the result is as expected.<\/p>\n\n\n\n

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

To get the expected result, I first created a Power BI DAX measure that sums up the sales column, but takes possible filters on the category column, but ignores other filters. This creates the basis for displaying sales by category, but ignores all other filters.<\/p>\n\n\n\n

Total Sales per Category = \r\nCALCULATE(\r\n    SUM(sales[Sales]),\r\n    ALLEXCEPT(sales, sales[Category])\r<\/code><\/pre>\n\n\n\n
In the next step, the sales are sorted based on this measure, a ranking key figure is created and sorted in descending order. This gives the sales by category a number<\/p>\n\n\n\n
RankByTotalSales = \r\nVAR CurrentCategory = MAX(sales[category])\r\nVAR TotalSalesForCurrentCategory = [Total Sales per Category]\r\nRETURN\r\n    RANKX(\r\n        ALLSELECTED(sales[category]),\r\n        [Total Sales per Category],\r\n        TotalSalesForCurrentCategory,\r\n        DESC,\r\n        DENSE\r\n    )\r\n<\/code><\/pre>\n\n\n\n
This key figure can then be used to create a table or any visualization. All four data sets are initially displayed here. The first three rows can then be filtered using the sorting key figure.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
Now if category A is removed from the filter, and only categories C and B are still displayed, and not category D.<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"
The situation is relatively simple. I needed a Power BI measure where I could see the top three companies with the highest sales, depending on the filters set. A colleague told me that it would be relatively simple to use … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1416"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1416"}],"version-history":[{"count":1,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1416\/revisions"}],"predecessor-version":[{"id":1422,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1416\/revisions\/1422"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1416"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1416"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1416"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}