topic Re: Rank the sum of each column and visualize in a sorted 'top N' horisontal bar chart in DAX Commands and Tips
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Rank-the-sum-of-each-column-and-visualize-in-a-sorted-top-N/m-p/1783807#M37446
<P>Thanks for your feedback <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span> <BR />Yeah, this would probably work. My current problem is that all the columns are calculated colums in DAX. The columns does not excist in the power query environment. So using your solution I would need to be able to make the calculated colums in power query. It can probably be done. <BR /><BR />The "less work approach" would be to do this in DAX, if possible <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span> <BR /><BR /><BR /></P>Wed, 14 Apr 2021 06:46:34 GMTKahrax2021-04-14T06:46:34ZRank the sum of each column and visualize in a sorted 'top N' horisontal bar chart
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Rank-the-sum-of-each-column-and-visualize-in-a-sorted-top-N/m-p/1782537#M37415
<P>I'm banging my head against the wall on this one. I thought it would be easy, but for some reason I cannot get it to work. </P><P>Consider the following table<BR /><BR /></P><TABLE border="1"><TBODY><TR><TD>timestamp </TD><TD>Instrument A </TD><TD>Instrument B </TD><TD>Instrument C </TD><TD>Instrument D</TD></TR><TR><TD>10.01.2021 10:00:00</TD><TD>5</TD><TD>6</TD><TD>9</TD><TD>9</TD></TR><TR><TD>10.01.2021 11:00:00</TD><TD>6</TD><TD>2</TD><TD>3</TD><TD>9</TD></TR><TR><TD>11.01.2021 10:00:00</TD><TD>7</TD><TD>7</TD><TD>4</TD><TD>9</TD></TR><TR><TD>12.01.2021 10:00:00</TD><TD>5</TD><TD>3</TD><TD>3</TD><TD>4</TD></TR><TR><TD>13.01.2021 10:00:00</TD><TD>3</TD><TD>8</TD><TD>7</TD><TD>8</TD></TR><TR><TD>14.01.2021 10:00:00</TD><TD>9</TD><TD>6</TD><TD><P>3</P></TD><TD>4</TD></TR><TR><TD>14.01.2021 11:00:00</TD><TD>4</TD><TD>4</TD><TD>8</TD><TD>4</TD></TR></TBODY></TABLE><P><BR />What I want to do is <BR />1. Sum the value of each colum (in this case that would be 39, 36, 37, 47 from left to right)<BR />2. Show the sums in an 'Top N' chart sorted decending </P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="topN chart.png" style="width: 416px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/493898i89CB0F8500FB4B52/image-size/large?v=v2&px=999" role="button" title="topN chart.png" alt="topN chart.png" /></span></P><P>In my real world case I have some 20 columns and 50000 rows, but the principle is the same. </P><P> </P><P>Any tips ?</P>Tue, 13 Apr 2021 13:39:48 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Rank-the-sum-of-each-column-and-visualize-in-a-sorted-top-N/m-p/1782537#M37415Kahrax2021-04-13T13:39:48ZRe: Rank the sum of each column and visualize in a sorted 'top N' horisontal bar chart
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Rank-the-sum-of-each-column-and-visualize-in-a-sorted-top-N/m-p/1782655#M37418
<P>Hello,<BR /><BR />i have tried out this topic and i think i have way you can do it.<BR /><BR />What i did is, i went into PowerQuery and unpivoted the table<BR /><BR /><BR /></P><P>First it looks like your table and afterwards like this screenshot:</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="SmithT_0-1618323782163.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/493939i504B4EAA567EC431/image-size/medium?v=v2&px=400" role="button" title="SmithT_0-1618323782163.png" alt="SmithT_0-1618323782163.png" /></span></P><P><BR /><BR />And this would already work for getting the horizontal bar chart<BR /><BR />Axis would be your Attribute (Instrument)<BR />Value the Value <BR /><BR /></P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="SmithT_1-1618323878551.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/493942i97C9706EF3F64172/image-size/medium?v=v2&px=400" role="button" title="SmithT_1-1618323878551.png" alt="SmithT_1-1618323878551.png" /></span></P><P><BR /><BR />Now you can add a Top N Filter on the visualisation.<BR /><BR />Does this help you?<BR /><BR />Cheers<BR /><BR />Tyree</P><P><BR /><BR /></P><P> </P>Tue, 13 Apr 2021 14:25:38 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Rank-the-sum-of-each-column-and-visualize-in-a-sorted-top-N/m-p/1782655#M37418SmithT2021-04-13T14:25:38ZRe: Rank the sum of each column and visualize in a sorted 'top N' horisontal bar chart
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Rank-the-sum-of-each-column-and-visualize-in-a-sorted-top-N/m-p/1783807#M37446
<P>Thanks for your feedback <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span> <BR />Yeah, this would probably work. My current problem is that all the columns are calculated colums in DAX. The columns does not excist in the power query environment. So using your solution I would need to be able to make the calculated colums in power query. It can probably be done. <BR /><BR />The "less work approach" would be to do this in DAX, if possible <span class="lia-unicode-emoji" title=":slightly_smiling_face:">ðŸ™‚</span> <BR /><BR /><BR /></P>Wed, 14 Apr 2021 06:46:34 GMThttps://community.powerbi.com/t5/DAX-Commands-and-Tips/Rank-the-sum-of-each-column-and-visualize-in-a-sorted-top-N/m-p/1783807#M37446Kahrax2021-04-14T06:46:34Z