Forum Discussion
TOPn help please
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 |
| Manchester | Aggression |
| Manchester | Aggression |
| Manchester | Aggression |
| Manchester | Aggression |
| Manchester | Neglect |
| Manchester | Neglect |
| Edinburgh | Neglect |
| Edinburgh | Violence |
| Edinburgh | Violence |
| Edinburgh | Violence |
| Edinburgh | Violence |
| Edinburgh | Neglect |
| Newcastle | Emotional |
| Newcastle | Violence |
| Newcastle | Aggression |
| Newcastle | Emotional |
| Newcastle | Emotional |
| Newcastle | Emotional |
Table2
| Edinburgh | Violence | 4 |
| Manchester | Aggression | 4 |
| Newcastle | Neglect | 4 |
Thanks
I have seen almost excatly the same question posted with same data over the last few days, and assume you are a from class of students who have been given the same exam homework. I hope you are not cheating asking for help 🙄.
The question contains a trick. What happens if a locations have mote than one issues which have the top number of incidents.
For example in this data London has 5 incidents of Theft and Drugs
Location Issue Incident Manchester Aggression Incident 1 Manchester Aggression Incident 2 Manchester Aggression Incident 3 Manchester Aggression Incident 4 Manchester Neglect Incident 5 Manchester Neglect Incident 6 Edinburgh Neglect Incident 7 Edinburgh Violence Incident 8 Edinburgh Violence Incident 9 Edinburgh Violence Incident 10 Edinburgh Violence Incident 11 Edinburgh Neglect Incident 12 Newcastle Emotional Incident 13 Newcastle Violence Incident 14 Newcastle Aggression Incident 15 Newcastle Emotional Incident 16 Newcastle Emotional Incident 17 Newcastle Emotional Incident 18 London Theft Incident 19 London Theft Incident 20 London Theft Incident 21 London Theft Incident 22 London Theft Incident 23 London Drugs Incident 24 London Drugs Incident 25 London Drugs Incident 26 London Drugs Incident 27 London Drugs Incident 28 Manchester Theft Incident 29 Edinburgh Theft Incident 30 Newcastle Theft Incident 31 Manchester Theft Incident 32 Edinburgh Theft Incident 33 Newcastle Drugs Incident 34 Manchester Drugs Incident 35 Manchester Drugs Incident 36 Newcastle Drugs Incident 37 Click here to download my PBIX solution form Onedrive.
Please click thumbs up for the helpful suggestion
and click accept solution if it works. You can accept multiple solutions in the thread,
How it works
Incidents = // get the number of incidents for the context COUNTROWS(Facts)Highest by location = VAR mylocation = SELECTEDVALUE(Facts[Location]) VAR mymaxvalue = MAXX( VALUES(Facts[Issue]), [Incidents] ) RETURN mymaxvalueIssues with max = // get the current context location VAR myissue = SELECTEDVALUE(Facts[Location]) // get the maxium number of incidents by issue for the current location VAR mymaxvalue = MAXX( VALUES(Facts[Issue]), [Incidents] ) // create a temp table of issues (just for the context location) that equal the max value VAR mymaxissues = FILTER(VALUES(Facts[Issue]), [Incidents] = mymaxvalue) RETURN // delimit CONCATENATEX(mymaxissues,Facts[Issue], " ,")If you download the PBIX you will see there are also measures to get the loactions with highest incident rate for each type of issue.
4 Replies
RichOB , First create a meausre say
M1 = countrows(Table1)
Then create another measure for top 3 location
calculate([M1], keepfilters(TOPN(3, allselected(Table[Location]), [M1], Desc) ) )
use that in visual with location
Also check Window function
Learn Power BI: Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ
Hi RichOB , You can achieve this by the feature available in power BI filters section, where you can follow the steps
1.Select table you want to apply Top N filter
2. go to filters.
3. In your case select Location
4.Select filter type to Top N
5. Set the number of top rows you want to see on the table.
6.select the column/measure on what basis you want to filter location for Top N, in your case it seems count of issues.
Hope it helps, Please mark it as a solution accepted, if it helps to resolve your issue.
I have seen almost excatly the same question posted with same data over the last few days, and assume you are a from class of students who have been given the same exam homework. I hope you are not cheating asking for help 🙄.
The question contains a trick. What happens if a locations have mote than one issues which have the top number of incidents.
For example in this data London has 5 incidents of Theft and Drugs
Location Issue Incident Manchester Aggression Incident 1 Manchester Aggression Incident 2 Manchester Aggression Incident 3 Manchester Aggression Incident 4 Manchester Neglect Incident 5 Manchester Neglect Incident 6 Edinburgh Neglect Incident 7 Edinburgh Violence Incident 8 Edinburgh Violence Incident 9 Edinburgh Violence Incident 10 Edinburgh Violence Incident 11 Edinburgh Neglect Incident 12 Newcastle Emotional Incident 13 Newcastle Violence Incident 14 Newcastle Aggression Incident 15 Newcastle Emotional Incident 16 Newcastle Emotional Incident 17 Newcastle Emotional Incident 18 London Theft Incident 19 London Theft Incident 20 London Theft Incident 21 London Theft Incident 22 London Theft Incident 23 London Drugs Incident 24 London Drugs Incident 25 London Drugs Incident 26 London Drugs Incident 27 London Drugs Incident 28 Manchester Theft Incident 29 Edinburgh Theft Incident 30 Newcastle Theft Incident 31 Manchester Theft Incident 32 Edinburgh Theft Incident 33 Newcastle Drugs Incident 34 Manchester Drugs Incident 35 Manchester Drugs Incident 36 Newcastle Drugs Incident 37 Click here to download my PBIX solution form Onedrive.
Please click thumbs up for the helpful suggestion
and click accept solution if it works. You can accept multiple solutions in the thread,
How it works
Incidents = // get the number of incidents for the context COUNTROWS(Facts)Highest by location = VAR mylocation = SELECTEDVALUE(Facts[Location]) VAR mymaxvalue = MAXX( VALUES(Facts[Issue]), [Incidents] ) RETURN mymaxvalueIssues with max = // get the current context location VAR myissue = SELECTEDVALUE(Facts[Location]) // get the maxium number of incidents by issue for the current location VAR mymaxvalue = MAXX( VALUES(Facts[Issue]), [Incidents] ) // create a temp table of issues (just for the context location) that equal the max value VAR mymaxissues = FILTER(VALUES(Facts[Issue]), [Incidents] = mymaxvalue) RETURN // delimit CONCATENATEX(mymaxissues,Facts[Issue], " ,")If you download the PBIX you will see there are also measures to get the loactions with highest incident rate for each type of issue.
