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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

@Rygaard ,

For Rank Refer these links

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

https://databear.com/power-bi-dax-topn-function/

https://zebrabi.com/power-bi-dax-webinar/

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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