Forum Discussion

JamesBurke's avatar
JamesBurke
Helper III
2 years ago
Solved

Top N

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