Forum Discussion
Post PartisanNeed help arranging top N columns
Hi, Using Table1 below, I'm looking to have a table visual in my report that looks like Table2, which shows the location with the highest number of issues related to it. Is there a way to do this using Top N? or another measure, please?
Table1
| Location | Issue | Count |
| Manchester | Aggression | 12 |
| Manchester | Neglect | 20 |
| Manchester | Emotional | 9 |
| Edinburgh | Aggression | 11 |
| Edinburgh | Neglect | 5 |
| Edinburgh | Emotional | 10 |
| Newcastle | Aggression | 3 |
| Newcastle | Neglect | 6 |
| Newcastle | Emotional | 17 |
Table2
| Manchester | Neglect | 20 |
| Edinburgh | Aggression | 11 |
| Newcastle | Emotional | 17 |
Thanks
Hi RichOB ,
This [Count] is the calculated column for your sample data.
Does your pbix not have a [count] column, you might consider the following steps:
Place the column to visual and set it to the Count type as follows:
Create a measure：
Flag = var _table= SUMMARIZE( ALL('Test Table'),[Issue],[Location],"Count",COUNTX('Test Table',[Issue])) var _table2= ADDCOLUMNS( _table,"rank",RANKX(FILTER(_table,[Location]=EARLIER([Location])),[Count],,DESC,Dense)) var _rank= SUMX(FILTER(_table2,[Location]=MAX([Location])&&[Issue]=MAX([Issue])),[rank]) RETURN IF( _rank=1,1,0)Place [Flag]in Filters, set is=1, apply filter.
This is the related document, you can view this content:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
- danextian
Super User
Hi
If you want to show just the top3, you can rank your values Issue and Location by the sum of count and filter the table to show top 3.
Rank By Issue and Location = RANKX ( ALL ( 'TopN'[Issue], 'TopN'[Location] ), CALCULATE ( SUM ( 'TopN'[Count] ) ), , DESC, DENSE ) Thanks for the reply from danextian and Thejeswar , please allow me to add some more information:
Hi RichOB ,Here are the steps you can follow：
1. Create measure.
Measure = var _table= ADDCOLUMNS( 'Table',"rank", RANKX( FILTER(ALL('Table'), 'Table'[Location]=EARLIER('Table'[Location])),[Count],,DESC,Dense)) RETURN IF( SUMX(_table,[rank])=1,SUM('Table'[Count]),BLANK())2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
RichOBHi Anonymous thanks for your reply. Where exactly is [count] coming from in your measure please? Are you making an aditional count measure or column before making this measure?
- RichOB
Hi Anonymous thanks for your reply. Where exactly is [count] coming from in your measure please? Are you making an aditional count measure or column before making this measure?
Hi RichOB ,
This [Count] is the calculated column for your sample data.
Does your pbix not have a [count] column, you might consider the following steps:
Place the column to visual and set it to the Count type as follows:
Create a measure：
Flag = var _table= SUMMARIZE( ALL('Test Table'),[Issue],[Location],"Count",COUNTX('Test Table',[Issue])) var _table2= ADDCOLUMNS( _table,"rank",RANKX(FILTER(_table,[Location]=EARLIER([Location])),[Count],,DESC,Dense)) var _rank= SUMX(FILTER(_table2,[Location]=MAX([Location])&&[Issue]=MAX([Issue])),[rank]) RETURN IF( _rank=1,1,0)Place [Flag]in Filters, set is=1, apply filter.
This is the related document, you can view this content:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
