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

@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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.