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

[Rygaard]

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:

Store	Cust rank	cust.	Item	amount	DB%	Item Rank
1 of 10	1	Customer YY	Item 1	100	24	1
			Item 2	89	5	2
			Item 3	46	55	3
	.....		Item 10	12	66	10
	10	Customer AA	item 1	120	5	1
			item 10	5	5	10
2 of 10	1	Customer AA	Item 1
			Item 2
			Item 3
			Item 4

or 

Rank pr store		Item 1	item 2	item 3	Item 4	Item 5	Item 6	Item 7	Item 30
Store A
1	Customer AA	10000	200		5000	200		155	152
2	Customer DD	5000			2500	321	125	215	128
3	Customer FF	4500			2000	215	125	1000	22
4-9	...
10	Customer QQ	500	10	10	10				25
Store B
1	Customer AA	5000	500		250
2	Customer QQQ		500	1000				15	415

[amitchandak]

@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

[Rygaard]

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

[amitchandak]

@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

[Rygaard]

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

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)

[Rygaard]

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