Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

show my company and top 4 in visualization

Hi,

I hope you can assist me,

I need to show in a visualization top 5 companies with the biggest ebit , I use the top N filter, But even when the company I work for is not in the TOP 5 I need it to appear so that it will show my company and the TOP 4, what is the way to do that?

 

Thank you,

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

RANK = 
VAR __rnk = RANKX( ALLSELECTED( EBIT[Company] ), [Total] )
RETURN
    IF(
        MAX( EBIT[Company] ) = "My Company"
            || __rnk <= MAX( 'TOP'[Value] ),
        __rnk
    )

Record_2022_08_10_21_05_57_45.gif


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

2 REPLIES 2
CNENFRNL
Community Champion
Community Champion

RANK = 
VAR __rnk = RANKX( ALLSELECTED( EBIT[Company] ), [Total] )
RETURN
    IF(
        MAX( EBIT[Company] ) = "My Company"
            || __rnk <= MAX( 'TOP'[Value] ),
        __rnk
    )

Record_2022_08_10_21_05_57_45.gif


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

jdbuchanan71
Super User
Super User

@Anonymous 

You can write a measure that calcualtes the [EBIT] for the top 5 and your company and filters the results to that list of companies.

Here is an example using a [Sales Amount] measure but the pattern would be the same, just need to change the names to match your model.

Top 5 + my company = 
VAR _TOPN = TOPN(5,ALL('Customer'[Company]),[Sales Amount])
VAR _MyCompany = FILTER(VALUES('Customer'[Company]),'Customer'[Company] = "AlbanyCompany")
VAR _List = UNION(_TOPN,_MyCompany)
RETURN
CALCULATE(
    [Sales Amount],
    KEEPFILTERS(_List)
)

You can see the measure returns the top 5 plus the company I have marked as _MyCompany even though their amount is lower then others:

jdbuchanan71_0-1660144641677.png

This will show 6 if your company is not in the top 5 or only 5 if your company is in the top 5.

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors