Calculating Top N across columns

Question

Hello,&nbsp;I have a datasource that tracks initiatives and the projects that affect each initiative.&nbsp; Each initiative is a column and each project is a row.&nbsp; I've been asked to show a Top N (Top 3 Initiatives by count of projects) across these initiatives.&nbsp; I Is there a way to calculate a Top N across columns w/o modifying the data model?&nbsp; If not, what is the best practice approach to doing so?

anonymous · Answer

hi&nbsp;Clint&nbsp;&nbsp;You can try this:&nbsp;-In the visual level filters on the right side of your report as shown in the screesnhot below:-in the BY Value , you can drag the Project field from your dataset and chnage it to count.- Change pay plan field to your required field&nbsp;Hope this helps!&nbsp;

clint · Answer

Hello&nbsp;Anonymous&nbsp;,&nbsp;To clarify, I need to calculate the Top N across values in multiple columns, not just one.&nbsp; I have something like 7 columns representing various attributes and each row in the dataset is a project name.&nbsp; I need to find the Top N (3) of project name count from all 7 columns; meaning which three 3 columns have the highest number of projects associated to them.

anonymous · Answer

Hi&nbsp;Clint&nbsp;,&nbsp;May be you can provide me some sample data to recreate the issue.&nbsp;Thanks,Tejaswi&nbsp;

clint · Answer

Hello&nbsp;Anonymous&nbsp;,&nbsp;Here is a dummy of what the table structure looks like (removing proprietary info)Project NameStatusClose ReasonMicro Stage GateDate CreatedClosure DateVarianceColumn1Column2Column3Column4Column5Column6Column7Column8Column9Column10Project 1Open&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;11&nbsp;1&nbsp;11project 2In progress&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11&nbsp;&nbsp;11&nbsp;&nbsp;&nbsp;Project 3Closed&nbsp;&nbsp;6/30/2019&nbsp;&nbsp;11&nbsp;&nbsp;&nbsp;1&nbsp;11&nbsp;Project 4Open&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;1&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi Clint,&nbsp;What I understood after looking at your dummy data is that, you want the summation of all the count in each column for all your projects and identify the highest summation for each of these columns.&nbsp;Correct me if I am wrong.if What i undersood is correct, then can you show the table somethign like this??This is because Power BI support doesn't support multiple Top N in Visual level filters.&nbsp;May be you need a Calculated column.&nbsp;you can refer this post :https://community.powerbi.com/t5/Desktop/TOPN-for-two-columns/td-p/545097&nbsp;&nbsp;&nbsp;Thanks,Tejaswi

Forum Discussion

Calculating Top N across columns

6 Replies

Related content

TMDL - calculated column

Direct Lake Model - Calculated Column/Tables

IF calculated column

Power Query or Calculated columns

Dynamic Variable in Calculated Columns