topic Dynamic Ranking in DAX Commands and Tips

topic Dynamic Ranking in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Ranking/m-p/1675029#M34296 <P>Hello All,</P><P>I have a dynamic problem to solve where I would like to rank my data dynamically on different measures and dimensions using slicers.</P><P>I have daily sales data which looks like this:</P><P> </P><TABLE><TBODY><TR><TD>Customer</TD><TD>Region</TD><TD>Date</TD><TD>Cost</TD><TD>Sales</TD><TD>InvOH</TD></TR><TR><TD>A</TD><TD>DFW</TD><TD>2/13/2021</TD><TD>42</TD><TD>82</TD><TD>18</TD></TR><TR><TD>B</TD><TD>AUS</TD><TD>2/13/2021</TD><TD>72</TD><TD>174</TD><TD>14</TD></TR><TR><TD>C</TD><TD>TYL</TD><TD>2/13/2021</TD><TD>92</TD><TD>74</TD><TD>23</TD></TR><TR><TD>D</TD><TD>WAX</TD><TD>2/13/2021</TD><TD>85</TD><TD>51</TD><TD>27</TD></TR><TR><TD>E</TD><TD>HOU</TD><TD>2/13/2021</TD><TD>41</TD><TD>104</TD><TD>11</TD></TR><TR><TD>F</TD><TD>LOS</TD><TD>2/13/2021</TD><TD>76</TD><TD>129</TD><TD>23</TD></TR><TR><TD>G</TD><TD>SCHL</TD><TD>2/13/2021</TD><TD>22</TD><TD>75</TD><TD>14</TD></TR><TR><TD>H</TD><TD>NSF</TD><TD>2/13/2021</TD><TD>20</TD><TD>170</TD><TD>20</TD></TR><TR><TD>I</TD><TD>DFW</TD><TD>2/13/2021</TD><TD>19</TD><TD>161</TD><TD>27</TD></TR><TR><TD>J</TD><TD>AUS</TD><TD>2/13/2021</TD><TD>48</TD><TD>139</TD><TD>12</TD></TR><TR><TD>K</TD><TD>TYL</TD><TD>2/13/2021</TD><TD>91</TD><TD>128</TD><TD>17</TD></TR><TR><TD>L</TD><TD>WAX</TD><TD>2/13/2021</TD><TD>44</TD><TD>159</TD><TD>20</TD></TR><TR><TD>M</TD><TD>HOU</TD><TD>2/13/2021</TD><TD>100</TD><TD>53</TD><TD>16</TD></TR><TR><TD>N</TD><TD>LOS</TD><TD>2/13/2021</TD><TD>30</TD><TD>157</TD><TD>19</TD></TR><TR><TD>O</TD><TD>SCHL</TD><TD>2/13/2021</TD><TD>18</TD><TD>158</TD><TD>24</TD></TR><TR><TD>P</TD><TD>NSF</TD><TD>2/13/2021</TD><TD>56</TD><TD>90</TD><TD>22</TD></TR><TR><TD>Q</TD><TD>DFW</TD><TD>2/13/2021</TD><TD>37</TD><TD>190</TD><TD>22</TD></TR><TR><TD>R</TD><TD>AUS</TD><TD>2/13/2021</TD><TD>10</TD><TD>145</TD><TD>24</TD></TR><TR><TD>S</TD><TD>TYL</TD><TD>2/13/2021</TD><TD>78</TD><TD>169</TD><TD>22</TD></TR><TR><TD>T</TD><TD>WAX</TD><TD>2/13/2021</TD><TD>72</TD><TD>173</TD><TD>14</TD></TR><TR><TD>U</TD><TD>HOU</TD><TD>2/13/2021</TD><TD>55</TD><TD>118</TD><TD>17</TD></TR><TR><TD>V</TD><TD>LOS</TD><TD>2/13/2021</TD><TD>90</TD><TD>151</TD><TD>17</TD></TR><TR><TD>W</TD><TD>SCHL</TD><TD>2/13/2021</TD><TD>38</TD><TD>80</TD><TD>11</TD></TR><TR><TD>X</TD><TD>NSF</TD><TD>2/13/2021</TD><TD>48</TD><TD>87</TD><TD>15</TD></TR><TR><TD>Y</TD><TD>DFW</TD><TD>2/13/2021</TD><TD>44</TD><TD>82</TD><TD>17</TD></TR><TR><TD>Z</TD><TD>AUS</TD><TD>2/13/2021</TD><TD>29</TD><TD>152</TD><TD>16</TD></TR></TBODY></TABLE><P> </P><P>I want to dynamically rank my stores or my region by total sales or total cost or total InvOH. I want to have slicer where I can select my dimension - stores / region and then select rack by sales / cost / Inv OH and my rank table displays data based on my selection.</P><P> </P><P>How do I achieve that? Please let me know if this does not make sense.</P> Thu, 18 Feb 2021 15:06:22 GMT sprakash1192 2021-02-18T15:06:22Z Dynamic Ranking https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Ranking/m-p/1675029#M34296 <P>Hello All,</P><P>I have a dynamic problem to solve where I would like to rank my data dynamically on different measures and dimensions using slicers.</P><P>I have daily sales data which looks like this:</P><P> </P><TABLE><TBODY><TR><TD>Customer</TD><TD>Region</TD><TD>Date</TD><TD>Cost</TD><TD>Sales</TD><TD>InvOH</TD></TR><TR><TD>A</TD><TD>DFW</TD><TD>2/13/2021</TD><TD>42</TD><TD>82</TD><TD>18</TD></TR><TR><TD>B</TD><TD>AUS</TD><TD>2/13/2021</TD><TD>72</TD><TD>174</TD><TD>14</TD></TR><TR><TD>C</TD><TD>TYL</TD><TD>2/13/2021</TD><TD>92</TD><TD>74</TD><TD>23</TD></TR><TR><TD>D</TD><TD>WAX</TD><TD>2/13/2021</TD><TD>85</TD><TD>51</TD><TD>27</TD></TR><TR><TD>E</TD><TD>HOU</TD><TD>2/13/2021</TD><TD>41</TD><TD>104</TD><TD>11</TD></TR><TR><TD>F</TD><TD>LOS</TD><TD>2/13/2021</TD><TD>76</TD><TD>129</TD><TD>23</TD></TR><TR><TD>G</TD><TD>SCHL</TD><TD>2/13/2021</TD><TD>22</TD><TD>75</TD><TD>14</TD></TR><TR><TD>H</TD><TD>NSF</TD><TD>2/13/2021</TD><TD>20</TD><TD>170</TD><TD>20</TD></TR><TR><TD>I</TD><TD>DFW</TD><TD>2/13/2021</TD><TD>19</TD><TD>161</TD><TD>27</TD></TR><TR><TD>J</TD><TD>AUS</TD><TD>2/13/2021</TD><TD>48</TD><TD>139</TD><TD>12</TD></TR><TR><TD>K</TD><TD>TYL</TD><TD>2/13/2021</TD><TD>91</TD><TD>128</TD><TD>17</TD></TR><TR><TD>L</TD><TD>WAX</TD><TD>2/13/2021</TD><TD>44</TD><TD>159</TD><TD>20</TD></TR><TR><TD>M</TD><TD>HOU</TD><TD>2/13/2021</TD><TD>100</TD><TD>53</TD><TD>16</TD></TR><TR><TD>N</TD><TD>LOS</TD><TD>2/13/2021</TD><TD>30</TD><TD>157</TD><TD>19</TD></TR><TR><TD>O</TD><TD>SCHL</TD><TD>2/13/2021</TD><TD>18</TD><TD>158</TD><TD>24</TD></TR><TR><TD>P</TD><TD>NSF</TD><TD>2/13/2021</TD><TD>56</TD><TD>90</TD><TD>22</TD></TR><TR><TD>Q</TD><TD>DFW</TD><TD>2/13/2021</TD><TD>37</TD><TD>190</TD><TD>22</TD></TR><TR><TD>R</TD><TD>AUS</TD><TD>2/13/2021</TD><TD>10</TD><TD>145</TD><TD>24</TD></TR><TR><TD>S</TD><TD>TYL</TD><TD>2/13/2021</TD><TD>78</TD><TD>169</TD><TD>22</TD></TR><TR><TD>T</TD><TD>WAX</TD><TD>2/13/2021</TD><TD>72</TD><TD>173</TD><TD>14</TD></TR><TR><TD>U</TD><TD>HOU</TD><TD>2/13/2021</TD><TD>55</TD><TD>118</TD><TD>17</TD></TR><TR><TD>V</TD><TD>LOS</TD><TD>2/13/2021</TD><TD>90</TD><TD>151</TD><TD>17</TD></TR><TR><TD>W</TD><TD>SCHL</TD><TD>2/13/2021</TD><TD>38</TD><TD>80</TD><TD>11</TD></TR><TR><TD>X</TD><TD>NSF</TD><TD>2/13/2021</TD><TD>48</TD><TD>87</TD><TD>15</TD></TR><TR><TD>Y</TD><TD>DFW</TD><TD>2/13/2021</TD><TD>44</TD><TD>82</TD><TD>17</TD></TR><TR><TD>Z</TD><TD>AUS</TD><TD>2/13/2021</TD><TD>29</TD><TD>152</TD><TD>16</TD></TR></TBODY></TABLE><P> </P><P>I want to dynamically rank my stores or my region by total sales or total cost or total InvOH. I want to have slicer where I can select my dimension - stores / region and then select rack by sales / cost / Inv OH and my rank table displays data based on my selection.</P><P> </P><P>How do I achieve that? Please let me know if this does not make sense.</P> Thu, 18 Feb 2021 15:06:22 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Ranking/m-p/1675029#M34296 sprakash1192 2021-02-18T15:06:22Z Re: Dynamic Ranking https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Ranking/m-p/1676590#M34338 <P><a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="217549" data-lia-user-login="sprakash1192" class="lia-mention lia-mention-user">sprakash1192</a> , Approch seems very similar to measure slicer. But might need use calculation group </P> <P> </P> <P>measure slicer <BR /><A href="https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115" target="_blank">https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115</A><BR /><A href="https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern" target="_blank">https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern </A></P> <P> </P> <P>calculation groups</P> <P><A href="https://www.youtube.com/watch?v=vlnx7QUVYME" target="_blank">https://www.youtube.com/watch?v=vlnx7QUVYME</A> //measure slicer </P> <P><BR /><A href="https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/" target="_blank">https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/</A></P> <P> </P> <P> </P> <P>For Rank Refer these links<BR /><A href="https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures" target="_blank">https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures</A><BR /><A href="https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns" target="_blank">https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns</A><BR /><A href="https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale" target="_blank">https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale</A><BR /><BR /></P> Fri, 19 Feb 2021 08:07:17 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Ranking/m-p/1676590#M34338 amitchandak 2021-02-19T08:07:17Z Re: Dynamic Ranking https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Ranking/m-p/1676595#M34339 <P>Hi,</P> <P>You could unpivot and work with a rank formula that works with allselected. Please see below and attached. </P> <P>Hope it helps.</P> <P>Kind regards, Steve. </P> <P><img /></P> <P> </P> <P> </P> Fri, 19 Feb 2021 08:09:02 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Ranking/m-p/1676595#M34339 stevedep 2021-02-19T08:09:02Z Re: Dynamic Ranking https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Ranking/m-p/1678407#M34399 <P>Just one kind of solution, create an independent table and then create a dynamic measure by switch function.</P><P><img /></P><P><img /></P><P><img /></P><P><A href="https://crocsconnect-my.sharepoint.com/:u:/g/personal/jazhang_crocs_com/EYlGXRpW-8RHlW26tJcjoW8B3GuoL2lWuffnsODyvFqC5A?e=JftEZx" target="_self">Dynamic Ranking.pbix</A> </P> Sat, 20 Feb 2021 03:37:39 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Ranking/m-p/1678407#M34399 jameszhang0805 2021-02-20T03:37:39Z Re: Dynamic Ranking https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Ranking/m-p/4001124#M155924 <P>how you create the rank calculation?</P> Thu, 20 Jun 2024 05:12:46 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Ranking/m-p/4001124#M155924 Anonymous 2024-06-20T05:12:46Z Re: Dynamic Ranking https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Ranking/m-p/4001433#M155937 <P>Hi <a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="285661" data-lia-user-login="jameszhang0805" class="lia-mention lia-mention-user">jameszhang0805</a> ,</P><P> </P><P>Sorry, the statement you shared is overdue.</P><P>I have the same question, how do you create a Rank calculation column or metric?</P> Thu, 20 Jun 2024 07:58:17 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-Ranking/m-p/4001433#M155937 Hugh-Guan 2024-06-20T07:58:17Z