Measure to display Top 3 project names with highest Headcount in the dynamic text box

[Prabhu_PowerBI]

Dear Team,

I'm pretty new to Power BI platform. Need your input to create the DAX measure to fetch the Top 3 projects with highest Headcount. This name of the project to be displayed in the dyanmic text box. Appreciate your input.

Project Name      Headcount

Project A              10000

Project B              12000

Project C              11000

Project D              8000

Project E               7000    

Answer should be

Project with top three headcounts are Project C(11000), Project B(12000) and Project A(10000).  This text should be displayed in the Text box as per the Slicer selection.

Many Thanks

Prabhu

[Anonymous · Accepted solution]

Hi @Prabhu_PowerBI ,

You can add these measures below:

Top3 = 
VAR _rank =
    RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[Headcount] ) ),, DESC )
VAR _result1 =
    IF ( _rank <= 3, SELECTEDVALUE ( 'Table'[Project Name] ), BLANK () )
VAR _result2 =
    IF ( _rank <= 3, SELECTEDVALUE ( 'Table'[Headcount] ), BLANK () )
RETURN
    IF ( _rank <= 3, _result1 & "(" & _result2 & ")", BLANK () )

TEXT = 
CONCATENATE (
    "Project with top three headcounts are:",
    CONCATENATEX ( 'Table', [Top3] )
)

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @lbendlin  and @ExcelMonke  for their prompt replies. I believe their reply is helpful to you.
Hi @Prabhu_PowerBI ,
Based on the information you provided, I have created a sample data, you can add a new measure:

Top3 = 
VAR _rank =
    RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[Headcount] ) ),, DESC )
VAR _result1 =
    IF ( _rank <= 3, SELECTEDVALUE ( 'Table'[Project Name] ), BLANK () )
VAR _result2 =
    IF ( _rank <= 3, SELECTEDVALUE ( 'Table'[Headcount] ), BLANK () )
RETURN
    IF ( _rank <= 3, _result1 & "(" & _result2 & ")", BLANK () )

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Prabhu_PowerBI]

Hi Ada Wang, Ibendlin, ExcelMonke,

Many Thanks for your response. I'm trying to pass the top 3 project names as input to the text box to provide insights to the client. Within the Text box, the below summary should appear:

"Project with top three headcounts are Project C(11000), Project B(12000) and Project A(10000)".

Using the below Measure, i'm able to fetch the project with highest headcount; however I'm facing challenges to fetch the second and third highest headcount project names.

Top_Project_HC =

TOPN(

    1,

    VALUES('Table Name'[Project Name]),

    [Count_of_HC]

)

Appreciate your support.

Many Thanks,

Prabhu

[lbendlin]

Change to TOPN(3,...) and then use CONCATENATEX.

[lbendlin]

use TOPN and CONCATENATEX.

[ExcelMonke]

Hello, 
Consider the following measure: 

Top3 =
RANKX(
FactTable,
FactTable[Headcount],
DESC
)

Then create a table with your two columns (Project and Headcount). Whilst your table is selected, add this measure to the "Filters on this Visual" portion of the filters pane, and filter to the measure is equal or less than "3"