topic Re: RANKX Function in Developer https://community.fabric.microsoft.com/t5/Developer/RANKX-Function/m-p/1494934#M26470 <P>Hi,&nbsp;<a href="https://community.fabric.microsoft.com/t5/user/viewprofilepage/user-id/268671">@burnaNayak</a>&nbsp;, you're only one inch away from your goal.</P><P>But there's a glitch in your measure, which throws error in such a viz</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Screenshot 2020-11-16 020350.png" style="width: 400px;"><img src="https://community.fabric.microsoft.com/t5/image/serverpage/image-id/397116i17E6D7EB41E61D16/image-size/medium?v=v2&amp;px=400" role="button" title="Screenshot 2020-11-16 020350.png" alt="Screenshot 2020-11-16 020350.png" /></span></P><P>&nbsp;</P><P>Therefore, I amend it and author another measure to achieve the goal as you described.</P><LI-CODE lang="markup">CountryRank amended = RANKX ( ALLSELECTED ( user_first_deposit ), CALCULATE ( SUM ( user_first_deposit[usd_amount] ) ) ) //////////////////// TOP4 = IF( [CountryRank amended] &lt;= 4, MAX(user_first_deposit[country]), "Other")</LI-CODE> Mon, 16 Nov 2020 01:09:51 GMT CNENFRNL 2020-11-16T01:09:51Z RANKX Function https://community.fabric.microsoft.com/t5/Developer/RANKX-Function/m-p/1494881#M26469 <P>Hi ,</P><P>&nbsp;</P><P>I Have applied Ranx to my Table which looks as below . In the first snapshot below have grouped the countries to show the Sales amount in Desc Order .</P><DIV><DIV><SPAN>CountryRank = RANKX(ALLSELECTED(user_first_deposit[country]),CALCULATE(SUM(user_first_deposit[usd_amount]))) . Now i Want another column "Amount TOP4 + Others " , where it shows top 4 countries and anything that exceeds 4 should be listed under others , the second snapshot is an example of how it should look like . Can anybody help me in this .</SPAN></DIV><DIV><SPAN>1ST SNAPSHOT</SPAN></DIV></DIV><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="burnaNayak_0-1605483220532.png" style="width: 400px;"><img src="https://community.fabric.microsoft.com/t5/image/serverpage/image-id/397097iE44CE5CA52FF047C/image-size/medium?v=v2&amp;px=400" role="button" title="burnaNayak_0-1605483220532.png" alt="burnaNayak_0-1605483220532.png" /></span></P><P>2 ND SNAPSHOT</P><TABLE><TBODY><TR><TD>country</TD><TD>CountryRank</TD><TD>usd_amount</TD><TD>Amount TOP4 + Others</TD></TR><TR><TD>China</TD><TD>1</TD><TD>128393088</TD><TD>China</TD></TR><TR><TD>Australia</TD><TD>2</TD><TD>33131855</TD><TD>Australia</TD></TR><TR><TD>Taiwan</TD><TD>3</TD><TD>4507356</TD><TD>Taiwan</TD></TR><TR><TD>Hong Kong SAR China</TD><TD>4</TD><TD>2191428</TD><TD>Hong Kong SAR China</TD></TR><TR><TD>New Zealand</TD><TD>5</TD><TD>2037118</TD><TD>Others&nbsp;</TD></TR><TR><TD>Singapore</TD><TD>6</TD><TD>1383320</TD><TD>Others&nbsp;</TD></TR><TR><TD>Malaysia</TD><TD>7</TD><TD>1280955</TD><TD>Others&nbsp;</TD></TR><TR><TD>Philippines</TD><TD>8</TD><TD>1128673</TD><TD>Others&nbsp;</TD></TR><TR><TD>Canada</TD><TD>9</TD><TD>910346</TD><TD>Others&nbsp;</TD></TR><TR><TD>United Kingdom</TD><TD>10</TD><TD>687390</TD><TD>Others&nbsp;</TD></TR><TR><TD>Cambodia</TD><TD>11</TD><TD>611389</TD><TD>Others&nbsp;</TD></TR><TR><TD>Switzerland</TD><TD>12</TD><TD>411285</TD><TD>Others&nbsp;</TD></TR><TR><TD>United Arab Emirates</TD><TD>13</TD><TD>342534</TD><TD>Others&nbsp;</TD></TR><TR><TD>Netherlands</TD><TD>14</TD><TD>330287</TD><TD>Others&nbsp;</TD></TR><TR><TD>Turkey</TD><TD>15</TD><TD>286855</TD><TD>Others&nbsp;</TD></TR><TR><TD>South Korea</TD><TD>16</TD><TD>256078</TD><TD>Others&nbsp;</TD></TR><TR><TD>Italy</TD><TD>17</TD><TD>243511</TD><TD>Others&nbsp;</TD></TR><TR><TD>Macau SAR China</TD><TD>18</TD><TD>225516</TD><TD>Others&nbsp;</TD></TR><TR><TD>Germany</TD><TD>19</TD><TD>220078</TD><TD>Others&nbsp;</TD></TR><TR><TD>Vietnam</TD><TD>20</TD><TD>159515</TD><TD>Others&nbsp;</TD></TR><TR><TD>Indonesia</TD><TD>21</TD><TD>140793</TD><TD>Others&nbsp;</TD></TR><TR><TD>Thailand</TD><TD>22</TD><TD>110061</TD><TD>Others&nbsp;</TD></TR><TR><TD>Norway</TD><TD>23</TD><TD>92062</TD><TD>Others&nbsp;</TD></TR><TR><TD>France</TD><TD>24</TD><TD>89876</TD><TD>Others&nbsp;</TD></TR><TR><TD>Spain</TD><TD>25</TD><TD>85145</TD><TD>Others&nbsp;</TD></TR><TR><TD>Peru</TD><TD>26</TD><TD>76861</TD><TD>Others&nbsp;</TD></TR><TR><TD>Portugal</TD><TD>27</TD><TD>65064</TD><TD>Others&nbsp;</TD></TR><TR><TD>United States</TD><TD>28</TD><TD>60863</TD><TD>Others&nbsp;</TD></TR><TR><TD>Belgium</TD><TD>29</TD><TD>49253</TD><TD>Others&nbsp;</TD></TR><TR><TD>Bhutan</TD><TD>30</TD><TD>49021</TD><TD>Others&nbsp;</TD></TR><TR><TD>Qatar</TD><TD>31</TD><TD>48396</TD><TD>Others&nbsp;</TD></TR><TR><TD>Japan</TD><TD>32</TD><TD>47374</TD><TD>Others&nbsp;</TD></TR><TR><TD>India</TD><TD>33</TD><TD>47196</TD><TD>Others&nbsp;</TD></TR><TR><TD>Morocco</TD><TD>34</TD><TD>43827</TD><TD>Others&nbsp;</TD></TR><TR><TD>Pakistan</TD><TD>35</TD><TD>38784</TD><TD>Others&nbsp;</TD></TR><TR><TD>Saudi Arabia</TD><TD>36</TD><TD>29448</TD><TD>Others&nbsp;</TD></TR><TR><TD>Kuwait</TD><TD>37</TD><TD>29294</TD><TD>Others&nbsp;</TD></TR><TR><TD>Luxembourg</TD><TD>38</TD><TD>28678</TD><TD>Others&nbsp;</TD></TR><TR><TD>Zambia</TD><TD>39</TD><TD>28132</TD><TD>Others&nbsp;</TD></TR><TR><TD>Brunei</TD><TD>40</TD><TD>27662</TD><TD>Others&nbsp;</TD></TR><TR><TD>Mexico</TD><TD>41</TD><TD>24950</TD><TD>Others&nbsp;</TD></TR><TR><TD>Ireland</TD><TD>42</TD><TD>23599</TD><TD>Others&nbsp;</TD></TR><TR><TD>Timor-Leste</TD><TD>43</TD><TD>20845</TD><TD>Others&nbsp;</TD></TR><TR><TD>Hungary</TD><TD>44</TD><TD>20674</TD><TD>Others&nbsp;</TD></TR><TR><TD>Finland</TD><TD>45</TD><TD>20314</TD><TD>Others&nbsp;</TD></TR><TR><TD>Sweden</TD><TD>46</TD><TD>19178</TD><TD>Others&nbsp;</TD></TR><TR><TD>Czech Republic</TD><TD>47</TD><TD>18720</TD><TD>Others&nbsp;</TD></TR><TR><TD>Romania</TD><TD>48</TD><TD>18278</TD><TD>Others&nbsp;</TD></TR><TR><TD>Slovakia</TD><TD>49</TD><TD>16703</TD><TD>Others&nbsp;</TD></TR><TR><TD>Denmark</TD><TD>50</TD><TD>14367</TD><TD>Others&nbsp;</TD></TR><TR><TD>South Africa</TD><TD>51</TD><TD>14327</TD><TD>Others&nbsp;</TD></TR><TR><TD>Cayman Islands</TD><TD>52</TD><TD>13500</TD><TD>Others&nbsp;</TD></TR><TR><TD>Russia</TD><TD>53</TD><TD>13233</TD><TD>Others&nbsp;</TD></TR><TR><TD>Hong Kong</TD><TD>54</TD><TD>13230</TD><TD>Others&nbsp;</TD></TR><TR><TD>U.S. Minor Outlying Islands</TD><TD>55</TD><TD>12900</TD><TD>Others&nbsp;</TD></TR><TR><TD>Nigeria</TD><TD>56</TD><TD>12170</TD><TD>Others&nbsp;</TD></TR><TR><TD>Austria</TD><TD>57</TD><TD>11459</TD><TD>Others&nbsp;</TD></TR><TR><TD>Greece</TD><TD>58</TD><TD>9917</TD><TD>Others&nbsp;</TD></TR><TR><TD>Bangladesh</TD><TD>59</TD><TD>8201</TD><TD>Others&nbsp;</TD></TR><TR><TD>Kenya</TD><TD>60</TD><TD>8008</TD><TD>Others&nbsp;</TD></TR><TR><TD>Brazil</TD><TD>61</TD><TD>6936</TD><TD>Others&nbsp;</TD></TR><TR><TD>SEf</TD><TD>62</TD><TD>6430</TD><TD>Others&nbsp;</TD></TR><TR><TD>Colombia</TD><TD>63</TD><TD>5330</TD><TD>Others&nbsp;</TD></TR><TR><TD>Poland</TD><TD>64</TD><TD>5143</TD><TD>Others&nbsp;</TD></TR><TR><TD>Lebanon</TD><TD>65</TD><TD>4673</TD><TD>Others&nbsp;</TD></TR><TR><TD>Cyprus</TD><TD>66</TD><TD>4583</TD><TD>Others&nbsp;</TD></TR><TR><TD>American Samoa</TD><TD>67</TD><TD>4567</TD><TD>Others&nbsp;</TD></TR><TR><TD>U.S. Virgin Islands</TD><TD>68</TD><TD>4295</TD><TD>Others&nbsp;</TD></TR><TR><TD>Maldives</TD><TD>69</TD><TD>4195</TD><TD>Others&nbsp;</TD></TR><TR><TD>Azerbaijan</TD><TD>70</TD><TD>4110</TD><TD>Others&nbsp;</TD></TR><TR><TD>Myanmar [Burma]</TD><TD>71</TD><TD>3957</TD><TD>Others&nbsp;</TD></TR><TR><TD>Mauritius</TD><TD>72</TD><TD>3553</TD><TD>Others&nbsp;</TD></TR><TR><TD>Iceland</TD><TD>73</TD><TD>3380</TD><TD>Others&nbsp;</TD></TR><TR><TD>Bulgaria</TD><TD>74</TD><TD>3090</TD><TD>Others&nbsp;</TD></TR><TR><TD>Oman</TD><TD>75</TD><TD>2900</TD><TD>Others&nbsp;</TD></TR><TR><TD>Bahrain</TD><TD>76</TD><TD>2300</TD><TD>Others&nbsp;</TD></TR><TR><TD>Israel</TD><TD>77</TD><TD>2118</TD><TD>Others&nbsp;</TD></TR><TR><TD>Monaco</TD><TD>78</TD><TD>2100</TD><TD>Others&nbsp;</TD></TR><TR><TD>Belarus</TD><TD>79</TD><TD>1771</TD><TD>Others&nbsp;</TD></TR><TR><TD>Jordan</TD><TD>80</TD><TD>1600</TD><TD>Others&nbsp;</TD></TR><TR><TD>Guam</TD><TD>81</TD><TD>1500</TD><TD>Others&nbsp;</TD></TR><TR><TD>Guatemala</TD><TD>82</TD><TD>1460</TD><TD>Others&nbsp;</TD></TR><TR><TD>Afghanistan</TD><TD>83</TD><TD>1372</TD><TD>Others&nbsp;</TD></TR><TR><TD>Sri Lanka</TD><TD>84</TD><TD>1347</TD><TD>Others&nbsp;</TD></TR><TR><TD>Lithuania</TD><TD>85</TD><TD>1345</TD><TD>Others&nbsp;</TD></TR><TR><TD>Martinique</TD><TD>86</TD><TD>1034</TD><TD>Others&nbsp;</TD></TR><TR><TD>Argentina</TD><TD>87</TD><TD>1000</TD><TD>Others&nbsp;</TD></TR><TR><TD>Botswana</TD><TD>87</TD><TD>1000</TD><TD>Others&nbsp;</TD></TR><TR><TD>Panama</TD><TD>87</TD><TD>1000</TD><TD>Others&nbsp;</TD></TR><TR><TD>Tajikistan</TD><TD>90</TD><TD>994</TD><TD>Others&nbsp;</TD></TR><TR><TD>Benin</TD><TD>91</TD><TD>899</TD><TD>Others&nbsp;</TD></TR><TR><TD>Jamaica</TD><TD>92</TD><TD>719</TD><TD>Others&nbsp;</TD></TR><TR><TD>Uganda</TD><TD>93</TD><TD>715</TD><TD>Others&nbsp;</TD></TR><TR><TD>Egypt</TD><TD>94</TD><TD>696</TD><TD>Others&nbsp;</TD></TR><TR><TD>Saint Martin</TD><TD>95</TD><TD>602</TD><TD>Others&nbsp;</TD></TR><TR><TD>Slovenia</TD><TD>96</TD><TD>600</TD><TD>Others&nbsp;</TD></TR><TR><TD>Uzbekistan</TD><TD>97</TD><TD>570</TD><TD>Others&nbsp;</TD></TR><TR><TD>Malta</TD><TD>98</TD><TD>500</TD><TD>Others&nbsp;</TD></TR><TR><TD>Croatia</TD><TD>99</TD><TD>411</TD><TD>Others&nbsp;</TD></TR><TR><TD>Ukraine</TD><TD>100</TD><TD>404</TD><TD>Others&nbsp;</TD></TR><TR><TD>Ecuador</TD><TD>101</TD><TD>300</TD><TD>Others&nbsp;</TD></TR><TR><TD>Tanzania</TD><TD>101</TD><TD>300</TD><TD>Others&nbsp;</TD></TR><TR><TD>Moldova</TD><TD>103</TD><TD>250</TD><TD>Others&nbsp;</TD></TR><TR><TD>Georgia</TD><TD>104</TD><TD>200</TD><TD>Others&nbsp;</TD></TR><TR><TD>Paraguay</TD><TD>104</TD><TD>200</TD><TD>Others&nbsp;</TD></TR><TR><TD>Chile</TD><TD>106</TD><TD>191</TD><TD>Others&nbsp;</TD></TR><TR><TD>Estonia</TD><TD>107</TD><TD>170</TD><TD>Others&nbsp;</TD></TR><TR><TD>Cuba</TD><TD>108</TD><TD>168</TD><TD>Others&nbsp;</TD></TR><TR><TD>Mozambique</TD><TD>109</TD><TD>140</TD><TD>Others&nbsp;</TD></TR><TR><TD>Ghana</TD><TD>110</TD><TD>120</TD><TD>Others&nbsp;</TD></TR><TR><TD>Latvia</TD><TD>111</TD><TD>110</TD><TD>Others&nbsp;</TD></TR><TR><TD>Equatorial Guinea</TD><TD>112</TD><TD>104</TD><TD>Others&nbsp;</TD></TR><TR><TD>Costa Rica</TD><TD>113</TD><TD>100</TD><TD>Others&nbsp;</TD></TR><TR><TD>Dominican Republic</TD><TD>113</TD><TD>100</TD><TD>Others&nbsp;</TD></TR><TR><TD>Nepal</TD><TD>113</TD><TD>100</TD><TD>Others&nbsp;</TD></TR><TR><TD>Northern Mariana Islands</TD><TD>113</TD><TD>100</TD><TD>Others&nbsp;</TD></TR><TR><TD>Serbia</TD><TD>113</TD><TD>100</TD><TD>Others&nbsp;</TD></TR><TR><TD>Suriname</TD><TD>113</TD><TD>100</TD><TD>Others&nbsp;</TD></TR><TR><TD>Unknown or Invalid Region</TD><TD>113</TD><TD>100</TD><TD>Others&nbsp;</TD></TR><TR><TD>EN</TD><TD>120</TD><TD>99</TD><TD>Others&nbsp;</TD></TR></TBODY></TABLE><P>&nbsp;</P> Sun, 15 Nov 2020 23:42:18 GMT https://community.fabric.microsoft.com/t5/Developer/RANKX-Function/m-p/1494881#M26469 burnaNayak 2020-11-15T23:42:18Z Re: RANKX Function https://community.fabric.microsoft.com/t5/Developer/RANKX-Function/m-p/1494934#M26470 <P>Hi,&nbsp;<a href="https://community.fabric.microsoft.com/t5/user/viewprofilepage/user-id/268671">@burnaNayak</a>&nbsp;, you're only one inch away from your goal.</P><P>But there's a glitch in your measure, which throws error in such a viz</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Screenshot 2020-11-16 020350.png" style="width: 400px;"><img src="https://community.fabric.microsoft.com/t5/image/serverpage/image-id/397116i17E6D7EB41E61D16/image-size/medium?v=v2&amp;px=400" role="button" title="Screenshot 2020-11-16 020350.png" alt="Screenshot 2020-11-16 020350.png" /></span></P><P>&nbsp;</P><P>Therefore, I amend it and author another measure to achieve the goal as you described.</P><LI-CODE lang="markup">CountryRank amended = RANKX ( ALLSELECTED ( user_first_deposit ), CALCULATE ( SUM ( user_first_deposit[usd_amount] ) ) ) //////////////////// TOP4 = IF( [CountryRank amended] &lt;= 4, MAX(user_first_deposit[country]), "Other")</LI-CODE> Mon, 16 Nov 2020 01:09:51 GMT https://community.fabric.microsoft.com/t5/Developer/RANKX-Function/m-p/1494934#M26470 CNENFRNL 2020-11-16T01:09:51Z Re: RANKX Function https://community.fabric.microsoft.com/t5/Developer/RANKX-Function/m-p/1495006#M26471 <P>Hi ,&nbsp;</P><P>If I add the countryrankamended , it looks like the 1st snapshot&nbsp;</P><P>But once i add the Top 4 column , the 2nd snapshot changes as below and is not giving me what i expected .</P><P>1st Snapshot&nbsp;</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="burnaNayak_14-1605490699085.png" style="width: 400px;"><img src="https://community.fabric.microsoft.com/t5/image/serverpage/image-id/397150i2F20342D4A02583D/image-size/medium?v=v2&amp;px=400" role="button" title="burnaNayak_14-1605490699085.png" alt="burnaNayak_14-1605490699085.png" /></span></P><P>&nbsp;</P><P>&nbsp;</P><P>2nd Snapshot&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="burnaNayak_12-1605490609695.png" style="width: 400px;"><img src="https://community.fabric.microsoft.com/t5/image/serverpage/image-id/397146iD70EF12EB3C21B9C/image-size/medium?v=v2&amp;px=400" role="button" title="burnaNayak_12-1605490609695.png" alt="burnaNayak_12-1605490609695.png" /></span></P><P>&nbsp;</P><P>&nbsp;</P> Mon, 16 Nov 2020 01:40:02 GMT https://community.fabric.microsoft.com/t5/Developer/RANKX-Function/m-p/1495006#M26471 burnaNayak 2020-11-16T01:40:02Z Re: RANKX Function https://community.fabric.microsoft.com/t5/Developer/RANKX-Function/m-p/1495544#M26474 <P><a href="https://community.fabric.microsoft.com/t5/user/viewprofilepage/user-id/268671">@burnaNayak</a>&nbsp;<BR /><BR />Can you check this video from SQLBI, it can be useful for your to get the results you are after.</P><P><BR /><A href="https://www.youtube.com/watch?v=tgL7D3JTa_E" target="_blank">https://www.youtube.com/watch?v=tgL7D3JTa_E</A></P><P>________________________</P><P>If my answer was helpful, please consider <STRONG><I>Accept it as the solution</I></STRONG><I> to help the other members find it</I></P><P>Click on the <STRONG>Thumbs-Up icon </STRONG>if you like this reply <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P><P><A href="https://www.youtube.com/channel/UCKwBEguA8IlBubIobaOormg?sub_confirmation=1" target="_blank"><FONT color="blue">YouTube</FONT></A>&nbsp; <A href="https://linkedin.com/in/fowmy" target="_blank"><FONT color="blue">LinkedIn</FONT></A></P> Mon, 16 Nov 2020 06:48:33 GMT https://community.fabric.microsoft.com/t5/Developer/RANKX-Function/m-p/1495544#M26474 Fowmy 2020-11-16T06:48:33Z Re: RANKX Function https://community.fabric.microsoft.com/t5/Developer/RANKX-Function/m-p/1496086#M26476 <P>Hi,&nbsp;<a href="https://community.fabric.microsoft.com/t5/user/viewprofilepage/user-id/268671">@burnaNayak</a>&nbsp;, strange enough<span class="lia-unicode-emoji" title=":face_with_tears_of_joy:">😂</span> it's works at my side. I attached <A href="https://1drv.ms/u/s!AsDP3LYI8umsgTFBT09Hy2goctCa?e=vUJHQc" target="_blank" rel="noopener"><EM><STRONG>a file</STRONG></EM></A> for your further reference.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Screenshot 2020-11-16 112108.png" style="width: 400px;"><img src="https://community.fabric.microsoft.com/t5/image/serverpage/image-id/397567i45509AA35F8661ED/image-size/medium?v=v2&amp;px=400" role="button" title="Screenshot 2020-11-16 112108.png" alt="Screenshot 2020-11-16 112108.png" /></span></P> Mon, 16 Nov 2020 10:26:19 GMT https://community.fabric.microsoft.com/t5/Developer/RANKX-Function/m-p/1496086#M26476 CNENFRNL 2020-11-16T10:26:19Z