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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Rygaard
Resolver I
Resolver I

Rank top X customer (by amount) pr store - and then show top 10 items for each customer pr store

I have

6000 customers

1000 items

20 stores (sites)

2 million sales lines.

 

I already made a ranking of my customers prs site (but i suspect I will have to do something different) :

 

 

' Customer top X = 
IF (
    ISINSCOPE('Customer'[C_No+Name]),
    RANKX (
        CALCULATETABLE (
            VALUES ('Customer'[C_No+Name]),
            ALLSELECTED ( 'Customer'[C_No+Name])
        ),
        ['Amount]
    )
)

 

 

 

If i make a matrix and have store name as top row and C_No+Name] as 2. row  and add the " ' Customer top X" as value i get a nice show of all stores, and all customers ranked based on Amount - Now i add a filter and show only " ' Customer top X"<11 and i have a top 10 pr store.

 

Now i however wanted a top 10 items pr customer pr. site - and no matter how i twist and turn it...  (besides getting it to be really slow)  I cant make this happen.

 

im thinking something like:

StoreCust rankcust.

Item

amountDB%Item Rank
1 of 101Customer YYItem 1100241
   Item 28952
   Item 346553
 ..... Item 1012

66

10

 10Customer AA

item 1

120

5

1

   

item 10

5

5

10

2 of 101Customer AAItem 1   
   Item 2   
   Item 3   
   Item 4   

 

or 

Rank pr store Item 1item 2item 3Item 4Item 5Item 6Item 7Item 30
Store A         
1

Customer AA

10000200 5000200 155152
2Customer DD5000  2500321125215128
3Customer FF4500  2000215125100022
4-9...        
10Customer QQ500101010   25
Store B         
1Customer AA5000500 250    
2Customer QQQ 5001000   15415
6 REPLIES 6

@amitchandak  - did you read what I wrote or did yo just spam external sites with becaseu you have a bot that searches for keywords?

 

you are linking to the excat solotion i have already done my self, its the next part of the problem i want help to do.

@Rygaard , My understanding was you need the top 10 items per customer. That is what I shared the links. Can you share raw data and final output in a table format and crisp problem description? One can get lost in a big description.

 

Performace has two aspects when data is big, If you create a column it will increase load time, If you create it as a measure it may increase visual display time. Depend on power BI desktop/service hardware, performance may vary

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak  - yes sorry but it seems alot will post links to what I have already acomplished not only here but in general, perhaps i Need to work on my writing skills.

 

I have a customer top 10 pr. site.

 

now i would like a item top 10 for each of thouse top 10 customers, for each site.

 

so it is a top 10 items pr customer (that is in the former top 10). for each site.

harshnathani
Community Champion
Community Champion

Hi @Rygaard ,

 

 

See if this article helps you.

 

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

https://www.sqlbi.com/articles/showing-the-top-5-products-and-others-row/

 

 

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

you are linking to the only part I have already done -  I got that part done.

 

its the next part that is hard

 

I have a top X   (rankX)  of the customers pr store.

 

Now i need a top 10 items for thouse top 10 customers, pr top 10 stores

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.