TOPN

[thliberato]

I have an Incidents table with 3 fields: Incident ID, System Name, Functional Area Name.

I need to make a chart that shows the TOPN systems with the highest number of incidents (Counter must be done by the ID field).

In addition, I would like to make a Dynamic Filter. I created a TopNOptions table with the values 3,5,10,15 and 20. I would like to insert in canvas a TOPNOptions filter and a TOPN BAR Graph X System (The Graph show the number of Systems selected in TOPNOPTION FILTER). Somebody can help me?

[austinsense · Accepted solution]

First, what do we want to rank by ... # of Incidents - let's make a measure for that:

NumIncidents:= COUNTROWS(IncidentsTable)

This assumes of course that the incidents table is one row per incident, if that wasn't the case then you'd want to do a distinctcount on the incidentid

NumIncidents:=DISTINCOUNT( IncidentsTable[IncidentsID] )

Second, we want to rank the systems by the number of incidents - let's make a measure for that

RankSystemsByIncident:=RANKX( ALL( IncidentsTable[System] ), [NumIncidents] )

The piece most people miss here is the ALL() - we don't want to rank the system against itself - we want to rank the system against all the other systems.

Third, we want to return a certain number of systems based on a choice - let's pretend for a second that we just want to return all the systems with their rankings - we would put the systems on the rows and drop the ranking in the values. PowerBI has a TOPN function that you could play around with.

Shoot, I have to run to a meeting but this should get you started 🙂

[thliberato · Accepted solution]

Thank you so much. It worked as expected.

[v-huizhn-msft · Accepted solution]

Hi @thliberato,

I am very glad to hear you have resolved your problem. In order to help more people, please mark the corresponding solution as answer. Thanks a lot.

Best Regards,
Angelia

[thliberato · Accepted solution]

Hi @v-huizhn-msft, I am new and I didnt find the function (mark as answer).

Can you Help me again ?

[austinsense · Accepted solution]

First, what do we want to rank by ... # of Incidents - let's make a measure for that:

NumIncidents:= COUNTROWS(IncidentsTable)

This assumes of course that the incidents table is one row per incident, if that wasn't the case then you'd want to do a distinctcount on the incidentid

NumIncidents:=DISTINCOUNT( IncidentsTable[IncidentsID] )

Second, we want to rank the systems by the number of incidents - let's make a measure for that

RankSystemsByIncident:=RANKX( ALL( IncidentsTable[System] ), [NumIncidents] )

The piece most people miss here is the ALL() - we don't want to rank the system against itself - we want to rank the system against all the other systems.

Third, we want to return a certain number of systems based on a choice - let's pretend for a second that we just want to return all the systems with their rankings - we would put the systems on the rows and drop the ranking in the values. PowerBI has a TOPN function that you could play around with.

Shoot, I have to run to a meeting but this should get you started 🙂

[thliberato · Accepted solution]

Thank you so much. It worked as expected.

[v-huizhn-msft · Accepted solution]

Hi @thliberato,

I am very glad to hear you have resolved your problem. In order to help more people, please mark the corresponding solution as answer. Thanks a lot.

Best Regards,
Angelia

[thliberato · Accepted solution]

Hi @v-huizhn-msft, I am new and I didnt find the function (mark as answer).

Can you Help me again ?

[v-huizhn-msft · Accepted solution]

Hi @thliberato,

Please review the folloowing screenshot, just click the "Accept as Solution" highlighted in red line.



Best Regards,
Angelia