topic Re: Create Measure that Counts and Ranks Another Measure and Updates when Filtered in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Create-Measure-that-Counts-and-Ranks-Another-Measure-and-Updates/m-p/4064371#M161358 <P>Appreciate the feedback.&nbsp; It looks like this will give expected results when I filter on ZIP, but not when I filter on County?</P> Fri, 26 Jul 2024 19:30:56 GMT patshannon11 2024-07-26T19:30:56Z Create Measure that Counts and Ranks Another Measure and Updates when Filtered https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Create-Measure-that-Counts-and-Ranks-Another-Measure-and-Updates/m-p/4061629#M161227 <P>My goal is to create a summary table&nbsp;containing a measure&nbsp;that scores each ZIP based on <STRONG>Facility 1 % Share</STRONG>, where the highest share has a score of 100%.&nbsp; I would also like the scoring to maintain these rules when filtering on either ZIP or County.&nbsp; I'm starting with a source data table similar to this:</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>ZIP</TD><TD>County</TD><TD>Facility</TD><TD>Volume</TD></TR><TR><TD>10001</TD><TD>County A</TD><TD>Facility 1</TD><TD>52</TD></TR><TR><TD>10002</TD><TD>County A</TD><TD>Facility 1</TD><TD>88</TD></TR><TR><TD>10003</TD><TD>County A</TD><TD>Facility 1</TD><TD>96</TD></TR><TR><TD>10004</TD><TD>County A</TD><TD>Facility 1</TD><TD>15</TD></TR><TR><TD>10005</TD><TD>County A</TD><TD>Facility 1</TD><TD>85</TD></TR><TR><TD>10006</TD><TD>County B</TD><TD>Facility 1</TD><TD>84</TD></TR><TR><TD>10007</TD><TD>County B</TD><TD>Facility 1</TD><TD>12</TD></TR><TR><TD>10008</TD><TD>County B</TD><TD>Facility 1</TD><TD>85</TD></TR><TR><TD>10009</TD><TD>County B</TD><TD>Facility 1</TD><TD>64</TD></TR><TR><TD>10010</TD><TD>County B</TD><TD>Facility 1</TD><TD>35</TD></TR><TR><TD>10001</TD><TD>County A</TD><TD>Facility 1</TD><TD>44</TD></TR><TR><TD>10002</TD><TD>County A</TD><TD>Facility 1</TD><TD>50</TD></TR><TR><TD>10003</TD><TD>County A</TD><TD>Facility 1</TD><TD>87</TD></TR><TR><TD>10004</TD><TD>County A</TD><TD>Facility 1</TD><TD>65</TD></TR><TR><TD>10005</TD><TD>County A</TD><TD>Facility 1</TD><TD>37</TD></TR><TR><TD>10006</TD><TD>County B</TD><TD>Facility 1</TD><TD>85</TD></TR><TR><TD>10007</TD><TD>County B</TD><TD>Facility 1</TD><TD>24</TD></TR><TR><TD>10008</TD><TD>County B</TD><TD>Facility 1</TD><TD>65</TD></TR><TR><TD>10009</TD><TD>County B</TD><TD>Facility 1</TD><TD>77</TD></TR><TR><TD>10010</TD><TD>County B</TD><TD>Facility 1</TD><TD>90</TD></TR><TR><TD>10001</TD><TD>County A</TD><TD>Facility 1</TD><TD>102</TD></TR><TR><TD>10002</TD><TD>County A</TD><TD>Facility 1</TD><TD>138</TD></TR><TR><TD>10003</TD><TD>County A</TD><TD>Facility 1</TD><TD>146</TD></TR><TR><TD>10004</TD><TD>County A</TD><TD>Facility 1</TD><TD>65</TD></TR><TR><TD>10005</TD><TD>County A</TD><TD>Facility 1</TD><TD>135</TD></TR><TR><TD>10006</TD><TD>County B</TD><TD>Facility 1</TD><TD>134</TD></TR><TR><TD>10007</TD><TD>County B</TD><TD>Facility 1</TD><TD>62</TD></TR><TR><TD>10008</TD><TD>County B</TD><TD>Facility 1</TD><TD>135</TD></TR><TR><TD>10009</TD><TD>County B</TD><TD>Facility 1</TD><TD>114</TD></TR><TR><TD>10010</TD><TD>County B</TD><TD>Facility 1</TD><TD>85</TD></TR><TR><TD>10001</TD><TD>County A</TD><TD>Facility 1</TD><TD>94</TD></TR><TR><TD>10002</TD><TD>County A</TD><TD>Facility 1</TD><TD>100</TD></TR><TR><TD>10003</TD><TD>County A</TD><TD>Facility 1</TD><TD>137</TD></TR><TR><TD>10004</TD><TD>County A</TD><TD>Facility 1</TD><TD>115</TD></TR><TR><TD>10005</TD><TD>County A</TD><TD>Facility 1</TD><TD>87</TD></TR><TR><TD>10006</TD><TD>County B</TD><TD>Facility 1</TD><TD>135</TD></TR><TR><TD>10007</TD><TD>County B</TD><TD>Facility 1</TD><TD>74</TD></TR><TR><TD>10008</TD><TD>County B</TD><TD>Facility 1</TD><TD>115</TD></TR><TR><TD>10009</TD><TD>County B</TD><TD>Facility 1</TD><TD>127</TD></TR><TR><TD>10010</TD><TD>County B</TD><TD>Facility 1</TD><TD>140</TD></TR><TR><TD>10001</TD><TD>County A</TD><TD>Facility 2</TD><TD>42</TD></TR><TR><TD>10002</TD><TD>County A</TD><TD>Facility 2</TD><TD>98</TD></TR><TR><TD>10003</TD><TD>County A</TD><TD>Facility 2</TD><TD>86</TD></TR><TR><TD>10004</TD><TD>County A</TD><TD>Facility 2</TD><TD>25</TD></TR><TR><TD>10005</TD><TD>County A</TD><TD>Facility 2</TD><TD>75</TD></TR><TR><TD>10006</TD><TD>County B</TD><TD>Facility 2</TD><TD>94</TD></TR><TR><TD>10007</TD><TD>County B</TD><TD>Facility 2</TD><TD>2</TD></TR><TR><TD>10008</TD><TD>County B</TD><TD>Facility 2</TD><TD>95</TD></TR><TR><TD>10009</TD><TD>County B</TD><TD>Facility 2</TD><TD>54</TD></TR><TR><TD>10010</TD><TD>County B</TD><TD>Facility 2</TD><TD>45</TD></TR><TR><TD>10001</TD><TD>County A</TD><TD>Facility 2</TD><TD>34</TD></TR><TR><TD>10002</TD><TD>County A</TD><TD>Facility 2</TD><TD>60</TD></TR><TR><TD>10003</TD><TD>County A</TD><TD>Facility 2</TD><TD>77</TD></TR><TR><TD>10004</TD><TD>County A</TD><TD>Facility 2</TD><TD>75</TD></TR><TR><TD>10005</TD><TD>County A</TD><TD>Facility 2</TD><TD>27</TD></TR><TR><TD>10006</TD><TD>County B</TD><TD>Facility 2</TD><TD>95</TD></TR><TR><TD>10007</TD><TD>County B</TD><TD>Facility 2</TD><TD>14</TD></TR><TR><TD>10008</TD><TD>County B</TD><TD>Facility 2</TD><TD>75</TD></TR><TR><TD>10009</TD><TD>County B</TD><TD>Facility 2</TD><TD>67</TD></TR><TR><TD>10010</TD><TD>County B</TD><TD>Facility 2</TD><TD>100</TD></TR><TR><TD>10001</TD><TD>County A</TD><TD>Facility 2</TD><TD>92</TD></TR><TR><TD>10002</TD><TD>County A</TD><TD>Facility 2</TD><TD>148</TD></TR><TR><TD>10003</TD><TD>County A</TD><TD>Facility 2</TD><TD>136</TD></TR><TR><TD>10004</TD><TD>County A</TD><TD>Facility 2</TD><TD>75</TD></TR><TR><TD>10005</TD><TD>County A</TD><TD>Facility 2</TD><TD>125</TD></TR><TR><TD>10006</TD><TD>County B</TD><TD>Facility 2</TD><TD>144</TD></TR><TR><TD>10007</TD><TD>County B</TD><TD>Facility 2</TD><TD>52</TD></TR><TR><TD>10008</TD><TD>County B</TD><TD>Facility 2</TD><TD>145</TD></TR><TR><TD>10009</TD><TD>County B</TD><TD>Facility 2</TD><TD>104</TD></TR><TR><TD>10010</TD><TD>County B</TD><TD>Facility 2</TD><TD>95</TD></TR><TR><TD>10001</TD><TD>County A</TD><TD>Facility 2</TD><TD>84</TD></TR><TR><TD>10002</TD><TD>County A</TD><TD>Facility 2</TD><TD>110</TD></TR><TR><TD>10003</TD><TD>County A</TD><TD>Facility 2</TD><TD>127</TD></TR><TR><TD>10004</TD><TD>County A</TD><TD>Facility 2</TD><TD>125</TD></TR><TR><TD>10005</TD><TD>County A</TD><TD>Facility 2</TD><TD>77</TD></TR><TR><TD>10006</TD><TD>County B</TD><TD>Facility 2</TD><TD>145</TD></TR><TR><TD>10007</TD><TD>County B</TD><TD>Facility 2</TD><TD>64</TD></TR><TR><TD>10008</TD><TD>County B</TD><TD>Facility 2</TD><TD>125</TD></TR><TR><TD>10009</TD><TD>County B</TD><TD>Facility 2</TD><TD>117</TD></TR><TR><TD>10010</TD><TD>County B</TD><TD>Facility 2</TD><TD>150</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I first created a measure for Facility 1 % Share using the DAX below:&nbsp;</P><P>&nbsp;</P><LI-CODE lang="markup">Facility 1 % Share = DIVIDE(CALCULATE(SUM('Table'[Volume]), 'Table'[Facility] IN { "Facility 1" }),SUM('Table'[Volume]))</LI-CODE><P>&nbsp;</P><P>&nbsp;</P><P><SPAN>I then created a measure for Facility Share Rank using the DAX below:</SPAN></P><P>&nbsp;</P><LI-CODE lang="markup">Facility 1 Share RANK = RANKX(ALLSELECTED('Table'[ZIP]),[Facility 1 % Share],,ASC,Skip)</LI-CODE><P>&nbsp;</P><P>&nbsp;</P><P><SPAN>I then created a measure for ZIP Count using the DAX below:</SPAN></P><P>&nbsp;</P><LI-CODE lang="markup">ZIP Count = CALCULATE(MAXX('Table','Table'[Facility 1 Share RANK]),ALL('Table'))</LI-CODE><P>&nbsp;</P><P>&nbsp;</P><P><SPAN>Lastly, created a measure for Score using the DAX below:</SPAN></P><P>&nbsp;</P><LI-CODE lang="markup">Score = [Facility 1 Share RANK]/[ZIP Count]</LI-CODE><P>&nbsp;</P><P>&nbsp;</P><P>The score works as intended without filters applied, but you can see in the 2nd and 3rd visual below when filtering by County or ZIP the score is not updating appropriately (in both cases scores should be 100%, 80%, 60%, 40%, and 20%).&nbsp; It appears my ZIP Count is not interacting with the filters - would appreciate any advice there or open to other approaches as well.</P><P>&nbsp;</P><P><img /></P><P>&nbsp;</P><P><img /></P><P>&nbsp;</P><P><img /></P> Thu, 25 Jul 2024 14:03:50 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Create-Measure-that-Counts-and-Ranks-Another-Measure-and-Updates/m-p/4061629#M161227 patshannon11 2024-07-25T14:03:50Z Re: Create Measure that Counts and Ranks Another Measure and Updates when Filtered https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Create-Measure-that-Counts-and-Ranks-Another-Measure-and-Updates/m-p/4061687#M161229 <P><FONT color="#000000"><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="666421" data-lia-user-login="patshannon11" class="lia-mention lia-mention-user">patshannon11</a>&nbsp;Hi!</FONT></P><P><FONT color="#000000">Try with these measures:</FONT></P><P>&nbsp;</P><DIV><DIV><FONT color="#000000">Facility 1 % Share =</FONT></DIV><DIV><FONT color="#000000">DIVIDE(</FONT></DIV><DIV><FONT color="#000000">&nbsp; &nbsp; CALCULATE(SUM('Table'[Volume]), 'Table'[Facility] = "Facility 1"), </FONT></DIV><DIV><FONT color="#000000">&nbsp; &nbsp; CALCULATE(SUM('Table'[Volume]))</FONT></DIV><DIV><FONT color="#000000">)</FONT></DIV><DIV>&nbsp;</DIV><DIV><DIV><DIV><FONT color="#000000">Facility 1 Share RANK =</FONT></DIV><DIV><FONT color="#000000">RANKX(</FONT></DIV><DIV><FONT color="#000000">&nbsp; &nbsp; ALLSELECTED('Table'[ZIP]), </FONT></DIV><DIV><FONT color="#000000">&nbsp; &nbsp; [Facility 1 % Share], </FONT></DIV><DIV><FONT color="#000000">&nbsp; &nbsp; , </FONT></DIV><DIV><FONT color="#000000">&nbsp; &nbsp; DESC, </FONT></DIV><DIV><FONT color="#000000">&nbsp; &nbsp; Skip</FONT></DIV><DIV><FONT color="#000000">)</FONT></DIV><DIV>&nbsp;</DIV><DIV><DIV><FONT color="#FF0000"><STRONG>ZIP Count =</STRONG></FONT></DIV><DIV><FONT color="#FF0000"><STRONG>COUNTROWS(ALLSELECTED('Table'[ZIP]))</STRONG></FONT></DIV></DIV><DIV>&nbsp;</DIV><DIV><DIV><DIV><FONT color="#000000">Score =</FONT></DIV><DIV><FONT color="#000000">DIVIDE([Facility 1 Share RANK], [ZIP Count])</FONT></DIV><DIV>&nbsp;</DIV><DIV><DIV><DIV>&nbsp;</DIV><DIV><FONT color="#000000"><img /></FONT><P>&nbsp;</P></DIV><DIV>&nbsp;</DIV><DIV><FONT color="#000000">BBF</FONT></DIV></DIV></DIV></DIV></DIV></DIV></DIV></DIV> Thu, 25 Jul 2024 14:55:33 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Create-Measure-that-Counts-and-Ranks-Another-Measure-and-Updates/m-p/4061687#M161229 BeaBF 2024-07-25T14:55:33Z Re: Create Measure that Counts and Ranks Another Measure and Updates when Filtered https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Create-Measure-that-Counts-and-Ranks-Another-Measure-and-Updates/m-p/4064371#M161358 <P>Appreciate the feedback.&nbsp; It looks like this will give expected results when I filter on ZIP, but not when I filter on County?</P> Fri, 26 Jul 2024 19:30:56 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Create-Measure-that-Counts-and-Ranks-Another-Measure-and-Updates/m-p/4064371#M161358 patshannon11 2024-07-26T19:30:56Z Re: Create Measure that Counts and Ranks Another Measure and Updates when Filtered https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Create-Measure-that-Counts-and-Ranks-Another-Measure-and-Updates/m-p/4066381#M161427 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="666421" data-lia-user-login="patshannon11" class="lia-mention lia-mention-user">patshannon11</a>&nbsp;modify these two measures:<BR /><BR /></P><DIV><DIV><SPAN>Facility </SPAN><SPAN>1</SPAN><SPAN> Share RANK = </SPAN></DIV><DIV><SPAN>RANKX</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>ALLSELECTED</SPAN><SPAN>('Table'[County], 'Table'[ZIP]), </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; [Facility 1 % Share], </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; , </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>DESC</SPAN><SPAN>, </SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; Skip</SPAN></DIV><DIV><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><DIV><DIV><SPAN>ZIP Count =</SPAN></DIV><DIV><SPAN>COUNTROWS</SPAN><SPAN>(</SPAN><SPAN>ALLSELECTED</SPAN><SPAN>(</SPAN><SPAN>'Table'</SPAN><SPAN>[ZIP]</SPAN><SPAN>, </SPAN><SPAN>'Table'</SPAN><SPAN>[County]</SPAN><SPAN>))</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>now it works also with County filter.</SPAN></DIV><DIV>&nbsp;</DIV><DIV><img /><P>&nbsp;</P></DIV><DIV><SPAN>If it's ok, please accept the answer as solution.</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN>BBF</SPAN></DIV></DIV></DIV></DIV> Mon, 29 Jul 2024 07:07:10 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Create-Measure-that-Counts-and-Ranks-Another-Measure-and-Updates/m-p/4066381#M161427 BeaBF 2024-07-29T07:07:10Z