Bottom 3 values filtering zeros

[Anonymous]

I have a case in which I'm struggling a bit.

I have a fact table that reflects votes of different teams (1 row=1 Vote) and a dimension table that contains the information of each team (Name, Team member Size). Both tables are related by TeamId

My purpose is to calculate the 3 top teams with the highest votes average per member and the top 3 bottom teams with less average of votes per member. The top 3 are not an issue.

The thing is that the dimension table could contain teams that are not voting and therefore the partitipacion is 0 %. I would like to exclude those from the visual.

When applying a filter on participation and a Bottom 3 to teams, seems that the bottom 3 is applied first and then is filtering out the participation 0. I have more than 3 values with participation =0, so the visual is being represented as empty.

I would like this to happen the other way around, first filter out the participation =0, and from the rest just pick the bottom 3.

I guess this filtering needs to be done in a measure, but I'm not able to give with the key.

Thanks in advance for the help! 

[Anonymous]

yep, thanks

[Icey · Accepted solution]

Hi @Anonymous ,

Please check if this is what you want:

Votes_ =
COUNT ( 'fact table'[Team] ) + 0

Bottom = 
VAR Rank_ =
    IF (
        [Votes_] > 0,
        RANKX (
            FILTER ( ALLSELECTED ( 'dimension table' ), [Votes_] > 0 ),
            CALCULATE ( [Votes_] ),
            ,
            ASC,
            DENSE
        )
    )
RETURN
    IF ( Rank_ >= 1 && Rank_ <= 3, 1 )

For more details, please check the attached .pbix file.

Best Regards,

Icey

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

[amitchandak]

@Anonymous , First create a measure like

if([participation] =0, blank(), [participation])

create TOP 3 on that