topic Re: Ranking and filetr for top N, for matrix table using DAX in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Ranking-and-filetr-for-top-N-for-matrix-table-using-DAX/m-p/3033245#M103773 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="460868" data-lia-user-login="FreemanZ" class="lia-mention lia-mention-user">FreemanZ</a>&nbsp;,</P><P>&nbsp;</P><P>I tried your suggestion, the result is kinda the same but filter out more data.</P><P>I still have multiple "area" (rows) ranking 1,2 and 3.</P><P>&nbsp;</P><P>I also forgot the mention, the data set provided here is the final version after being transformed in the query.</P><P>&nbsp;</P> Fri, 20 Jan 2023 07:34:03 GMT Zakk2000 2023-01-20T07:34:03Z Ranking and filetr for top N, for matrix table using DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Ranking-and-filetr-for-top-N-for-matrix-table-using-DAX/m-p/3032882#M103759 <P>Hi All,</P><P>I am trying to do ranking for the inner row for the matrix table and need help where I am stuck for several days on this.</P><P>&nbsp;</P><P>Sample Data:</P><TABLE><TBODY><TR><TD>Country</TD><TD>Area</TD><TD>Sales</TD></TR><TR><TD>US</TD><TD>A&nbsp;</TD><TD>$</TD></TR><TR><TD>US</TD><TD>B</TD><TD>$</TD></TR><TR><TD>Australia&nbsp;</TD><TD>G</TD><TD>$</TD></TR><TR><TD>US</TD><TD>C</TD><TD>$</TD></TR><TR><TD>US</TD><TD>D</TD><TD>$</TD></TR><TR><TD>China</TD><TD>H</TD><TD>$</TD></TR><TR><TD>US</TD><TD>E</TD><TD>$</TD></TR><TR><TD>US</TD><TD>F</TD><TD>$</TD></TR><TR><TD>US</TD><TD>G</TD><TD>$</TD></TR><TR><TD>China</TD><TD>H</TD><TD>$</TD></TR><TR><TD>China</TD><TD>I</TD><TD>$</TD></TR><TR><TD>China</TD><TD>J</TD><TD>$</TD></TR><TR><TD>US</TD><TD>A&nbsp;</TD><TD>$</TD></TR><TR><TD>China</TD><TD>K</TD><TD>$</TD></TR><TR><TD>China</TD><TD>L</TD><TD>$</TD></TR><TR><TD>China</TD><TD>M</TD><TD>$</TD></TR><TR><TD>Australia</TD><TD>N</TD><TD>$</TD></TR><TR><TD>Australia</TD><TD>O</TD><TD>$</TD></TR><TR><TD>US</TD><TD>A&nbsp;</TD><TD>$</TD></TR><TR><TD>Australia</TD><TD>P</TD><TD>$</TD></TR><TR><TD>Australia</TD><TD>Q</TD><TD>$</TD></TR><TR><TD>Australia</TD><TD>R</TD><TD>$</TD></TR><TR><TD>Australia</TD><TD>S</TD><TD>$</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I used this DAX function:</P><DIV><DIV><SPAN>Rank = </SPAN><SPAN>RANKX (</SPAN><SPAN>ALL (Report[Area]),&nbsp;</SPAN><SPAN>CALCULATE(SUM(Report[Sales]))</SPAN><SPAN>)</SPAN></DIV><DIV>&nbsp;</DIV><DIV><SPAN><SPAN>and use filter to filter up top 3 sales.</SPAN></SPAN></DIV><DIV><SPAN><SPAN>&nbsp;</SPAN></SPAN><img /><P>==============================================</P></DIV></DIV><P><STRONG>Result Received:</STRONG></P><TABLE><TBODY><TR><TD><STRONG>Country/Area</STRONG></TD><TD><STRONG>Sales</STRONG></TD><TD><STRONG>Rank (Result Received)</STRONG></TD></TR><TR><TD><STRONG>US</STRONG></TD><TD><STRONG>490000</STRONG></TD><TD><STRONG>1</STRONG></TD></TR><TR><TD>A</TD><TD>100000</TD><TD>1</TD></TR><TR><TD>G&nbsp;</TD><TD>90000</TD><TD>1</TD></TR><TR><TD>C</TD><TD>80000</TD><TD>1</TD></TR><TR><TD>D</TD><TD>70000</TD><TD>3</TD></TR><TR><TD>E</TD><TD>60000</TD><TD>3</TD></TR><TR><TD>F</TD><TD>50000</TD><TD>4</TD></TR><TR><TD>B</TD><TD>40000</TD><TD>1</TD></TR><TR><TD><STRONG>China</STRONG></TD><TD><STRONG>330000</STRONG></TD><TD><STRONG>2</STRONG></TD></TR><TR><TD>H</TD><TD>80000</TD><TD>1</TD></TR><TR><TD>M</TD><TD>70000</TD><TD>1</TD></TR><TR><TD>J</TD><TD>60000</TD><TD>1</TD></TR><TR><TD>K</TD><TD>50000</TD><TD>1</TD></TR><TR><TD>L</TD><TD>40000</TD><TD>1</TD></TR><TR><TD>I</TD><TD>30000</TD><TD>1</TD></TR><TR><TD><STRONG>Australia</STRONG></TD><TD><STRONG>210000</STRONG></TD><TD><STRONG>3</STRONG></TD></TR><TR><TD>N</TD><TD>60000</TD><TD>1</TD></TR><TR><TD>S</TD><TD>50000</TD><TD>2</TD></TR><TR><TD>P</TD><TD>40000</TD><TD>1</TD></TR><TR><TD>G</TD><TD>30000</TD><TD>1</TD></TR><TR><TD>R</TD><TD>20000</TD><TD>1</TD></TR><TR><TD>O</TD><TD>10000</TD><TD>4</TD></TR></TBODY></TABLE><P>&nbsp;</P><P><STRONG>Intended Result:</STRONG></P><TABLE><TBODY><TR><TD><STRONG>Country/Area</STRONG></TD><TD><STRONG>Sales</STRONG></TD><TD><FONT color="#000000"><STRONG>Intended result</STRONG></FONT></TD></TR><TR><TD><STRONG>US</STRONG></TD><TD><STRONG>490000</STRONG></TD><TD><FONT color="#000000">1</FONT></TD></TR><TR><TD>A</TD><TD>100000</TD><TD><FONT color="#000000">1</FONT></TD></TR><TR><TD>G&nbsp;</TD><TD>90000</TD><TD><FONT color="#000000">2</FONT></TD></TR><TR><TD>C</TD><TD>80000</TD><TD><FONT color="#000000">3</FONT></TD></TR><TR><TD><STRONG>China</STRONG></TD><TD><STRONG>330000</STRONG></TD><TD><FONT color="#000000"><STRONG>2</STRONG></FONT></TD></TR><TR><TD>H</TD><TD>80000</TD><TD><FONT color="#000000">1</FONT></TD></TR><TR><TD>M</TD><TD>70000</TD><TD><FONT color="#000000">2</FONT></TD></TR><TR><TD>J</TD><TD>60000</TD><TD><FONT color="#000000">3</FONT></TD></TR><TR><TD><STRONG>Australia</STRONG></TD><TD><STRONG>210000</STRONG></TD><TD><FONT color="#000000"><STRONG>3</STRONG></FONT></TD></TR><TR><TD>N</TD><TD>60000</TD><TD><FONT color="#000000">1</FONT></TD></TR><TR><TD>S</TD><TD>50000</TD><TD><FONT color="#000000">2</FONT></TD></TR><TR><TD>P</TD><TD>40000</TD><TD><FONT color="#000000">3</FONT></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Appreciate all the help on this <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Fri, 20 Jan 2023 02:59:40 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Ranking-and-filetr-for-top-N-for-matrix-table-using-DAX/m-p/3032882#M103759 Zakk2000 2023-01-20T02:59:40Z Re: Ranking and filetr for top N, for matrix table using DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Ranking-and-filetr-for-top-N-for-matrix-table-using-DAX/m-p/3032919#M103765 <P>hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="501859" data-lia-user-login="Zakk2000" class="lia-mention lia-mention-user">Zakk2000</a>&nbsp;</P><P>try like:</P><DIV><DIV><SPAN>RANKX</SPAN><SPAN>(</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>ALL</SPAN><SPAN>(</SPAN><SPAN>Report</SPAN><SPAN>[Area]</SPAN><SPAN>),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>CALCULATE</SPAN><SPAN>(</SPAN><SPAN>SUM</SPAN><SPAN>(</SPAN><SPAN>Report</SPAN><SPAN>[Sales]</SPAN><SPAN>)),</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; ,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; ,</SPAN></DIV><DIV><SPAN>&nbsp; &nbsp; </SPAN><SPAN>Dense</SPAN></DIV><DIV><SPAN>)</SPAN></DIV></DIV> Fri, 20 Jan 2023 03:31:39 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Ranking-and-filetr-for-top-N-for-matrix-table-using-DAX/m-p/3032919#M103765 FreemanZ 2023-01-20T03:31:39Z Re: Ranking and filetr for top N, for matrix table using DAX https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Ranking-and-filetr-for-top-N-for-matrix-table-using-DAX/m-p/3033245#M103773 <P>Hi&nbsp;<a href="javascript:void(0)" data-lia-user-mentions="" data-lia-user-uid="460868" data-lia-user-login="FreemanZ" class="lia-mention lia-mention-user">FreemanZ</a>&nbsp;,</P><P>&nbsp;</P><P>I tried your suggestion, the result is kinda the same but filter out more data.</P><P>I still have multiple "area" (rows) ranking 1,2 and 3.</P><P>&nbsp;</P><P>I also forgot the mention, the data set provided here is the final version after being transformed in the query.</P><P>&nbsp;</P> Fri, 20 Jan 2023 07:34:03 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Ranking-and-filetr-for-top-N-for-matrix-table-using-DAX/m-p/3033245#M103773 Zakk2000 2023-01-20T07:34:03Z