Top N

[JamesBurke]

Hi all , 

I want a measure that returns the value that is in 2 in the top N Value 

Topn = 
       CALCULATE(MAXX('Emporia Devices', 'Emporia Devices'[Device name KP]),FILTER(TOPN(
            1, 
            SUMMARIZE(
                    'Emporia Devices', 
                    'Emporia Devices'[Device name KP], 
                    "TotalSales", [Max Month Usages]
            ),
            [Max Month Usages], ASC
        ),
        [Max Month Usages]
))

Max Month Usages = 
Var MaxMonthValue = 
    CALCULATE(
        MAX('Date'[Year Month Number]),
        FILTER('Date',[UsagesCount] >= 1),
        ALL('Date'))
        
RETURN 
CALCULATE(
    [Total Kwh],
    FILTER( 
        ALL('Date'),
        'Date'[Year Month Number] = MaxMonthValue
        )
    )

UsagesCount = CALCULATE(COUNTROWS('Emporia Device Usage'))

I want to return the second postion rather then adding both 1 and 2 togther i have used a ranking system but i want this to change dynamically based on the latest month 

Thanks , James

[amitchandak · Accepted solution]

@JamesBurke , Try to use the index function for that

Continue to explore Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

Find Top & Bottom Performers| How to get 2nd Topper without Rank-Filter
https://www.youtube.com/watch?v=vipvw4F8LcE&list=PLPaNVDMhUXGZ5uQZ_v0tfC5lIjEHoa6Yk