Consecutive Data

[Anonymous]

This is a continuation of the thread below, which I'd already marked as solved, but have since noticed a problem:

https://community.powerbi.com/t5/Desktop/Consecutive-Top-7-finishes-in-league-table/m-p/561314#M2647...

I needed help with a formula to calculate people's consecutive finishes in a weekly game of cards based on whether they finished in the top 7 or not. I wanted to know somebody's best run (consecutively finishing in the top 7), worst run (consecutively failing to finish in the top 7), and their current run.

The dummy files we worked with seemed to work but when I've used my real data, I've exposed a problem with the current runs formula.

Below is a link to my file (with anaonymised real data). On the Current tab, on the top left visual, underneath the horizontal blue line there's an entry for a player called "Billy". His current run is showing -3 (the minus figure reflecting that this is an outside of the top 7 finish). However if we look at the bottom right visual, Billy has played 4 times, after the first 3 times he failed to make the top 7 and his current run would have bene -3, however his final tournament to date he did finish 7th so his current run should be 1, not -3.

I'm not sure why the formula is failing to take into account Billy's final tournament.

https://www.dropbox.com/s/6embrngp5nf4odp/DUMMY%20PKR%20Reports%20V3.pbix?dl=0

[v-yulgu-msft]

Hi @Anonymous,

Does this work in your scenario:

Current2 = 
MINX (
    TOPN (
        1,
        SUMMARIZE (
            'Results',
            Results[Player],
            Results[FinalIndex],
            "Counts", IF (
                LEFT ( MIN ( 'Results'[Result] ), 3 ) IN { "Low", "Fir" },
                 -COUNT ( Results[FinalIndex] ),
                COUNT ( Results[FinalIndex] )
            )
        ),
        [FinalIndex], ASC
    ),
    [Counts]
)

Regards,

Yuliana Gu

[Anonymous · Accepted solution]

Thanks for your response.

Your formula didn't work, but I managed to figure out the problem. The column FinalIndex was of type "Text" where it needed to be numeric - as such the value 9 was taking precedence over the vlue 10 in this case.

I have therefore stuck with the "Current" formula provided as a solution in the original post.