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!