Rank of top 2 values in category

[RobertO995]

Hello,

I've searched, but didn't find anything that fit my problem.

I have budgets of every store (every store has own ID). Every store is also in some region. I want to create other table that will present only stores with TOP2 budgets in every region.

This is my example data:

and the result should be like:

Region1 - 101

Region1 - 102

Region2 - 103

Region2 - 104

Region3 - 106

Region3 - 108

I tried use GROUPBY function, but then I can obtain only TOP1 store in Region, but cannot apply that solution to have table with 2 stores with the highest budget.

Thx for yuor help

[LivioLanzo · Accepted solution]

Hi @RobertO995 

you can use the GENERATE function for this:

GENERATE(
    ALLNOBLANKROW( Data[Region] ),
    CALCULATETABLE(
        TOPN(
            2,
            SELECTCOLUMNS(Data, "Budget", [Budget], "Store Number", [Store Number]),
            [Budget]
        )
    )
)