Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RobertO995
Frequent Visitor

Rank of top 2 values in category

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:

 

2019-03-01_09h41_47.png

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

1 ACCEPTED SOLUTION
LivioLanzo
Solution Sage
Solution Sage

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]
        )
    )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

1 REPLY 1
LivioLanzo
Solution Sage
Solution Sage

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]
        )
    )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors