Show a top 10 of the highest and lowest values in the same graph

[Syndicate_Admin]

Hello good afternoon everyone,

I have a graph in power bi that shows a calculation that is basically a division, and I need to show the top 10 of the highest values and at the same time the lowest ones that in my case are going to be negative values, I have to show this in a bar graph but I can't find a way to do it.

I hope you can help me.

[Ritaf1983]

Hi @Syndicate_Admin 
To achieve a goal you can create these 3 measures.
I use my example, you can modify it for your parameters.

I have a table with profits and let's try to show the top 10 and bottom 10 countries by profits.
1. Ranking descending ( to get top 10).

Rank top profits = RANKX(ALLSELECTED(Customers[Country]),[profits _ %],,DESC,Dense)

2. Ranking ascending (to get bottom 10)

Rank bottom profits = RANKX(ALLSELECTED(Customers[Country]),[profits _ %],,ASC,Dense)

3. Flag measure wich will assist us to filter the graph

flag_top_bottom =

var

topFlag = if([Rank top profits]>=1 && [Rank top profits]<=10,1,0)

var

bottomFlag =if([Rank bottom profits]>=1&&[Rank bottom profits]<=10,1,0)

Return

if(topFlag=1 || bottomFlag=1,1,0)

4. measure for coloring top and bottom with different colors ( optional)

TopFlagColor = if([Rank top profits]>=1 && [Rank top profits]<=10,"#2b83ba","#d7191c

")

Now let's drag  country , profits and all those to the graph:

for colors, we can use conditional formatting based on the measure :

Result :

Pbix is attached

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

[Syndicate_Admin]

Hello thank you very much for your answer, the situation I have is that the measurement I want the top 10 of seniors and minors in turn uses other measurements that in total use 3 different tables, I have not found how to adapt the measurement to that scenario.

[Ritaf1983]

Hi @Syndicate_Admin 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[aduguid]

Try this measure. Change [YourValue] out for the measure that is doing your division. 

TopAndBottom10Combined = 
VAR HighestRanked = 
    RANKX(ALL('YourTable'), [YourValue], , DESC, DENSE)
VAR LowestRanked = 
    RANKX(ALL('YourTable'), [YourValue], , ASC, DENSE)
RETURN
    IF(
        (HighestRanked <= 10) || (LowestRanked <= 10 && [YourValue] < 0),
        [YourValue],
        BLANK()
    )

[Syndicate_Admin]

I did not find a way to apply it because the measure I used in turn uses other measures that in total use data from three different tables, I do not know how it would be applied.