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
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 |
@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
@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.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |