Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone!
Any help will be appreciated in the next question
I have 3 tables:
1. Sales (date, customer ID, product ID, sales $)
2. Calendar (dates, months & so on)
3. Products (product ID, product name, brand, category and so on)
Sales is linked to Products by Product ID.
Sales is linked to Calendar by Date.
Sales is also linked to Customer List table by Client ID.
I need a measure to get 2 rankings of each product in 2 dimensions:
1. Rank of particular product inside it's parental brand in each customer's sales
2. Rank of particular product inside it's parental brand in the whole company's sales result.
The final target is to be able to compare, for example, product A1 inside brand A for customer X has the 4th place in it's (customer's) sales and at the same time this product is the 2nd in the sales of brand A in the company's totals.
Thanks a lot for your help!
Solved! Go to Solution.
Hi @D3K ,
That measure in the demo could be the customer level measure. Please try out this one as a company level rank measure.
CompanyLevelRank = RANKX ( ALL ( DimProduct[ProductKey] ), CALCULATE ( SUM ( FactSales[SalesQuantity] ), ALL ( DimCustomer[CustomerLabel] ) ) )
Best Regards,
Hi @D3K ,
One measure is enough. You can change the context to get what you want. Please download the demo from the attachment.
Measure = RANKX ( ALL ( DimProduct[ProductKey] ), CALCULATE ( SUM ( FactSales[SalesQuantity] ) ) )
Best Regards,
Hi @v-jiascu-msft Dale,
thank you for your answer. I've tried a hundreds of formula combinations, but the thing is the context 🙂
My problem was to get 1 common table of the next type:
Will use your solution with 2 tables, but maybe is it the way to create such table as I've described before?
Thanks a lot!
Hi @D3K ,
That measure in the demo could be the customer level measure. Please try out this one as a company level rank measure.
CompanyLevelRank = RANKX ( ALL ( DimProduct[ProductKey] ), CALCULATE ( SUM ( FactSales[SalesQuantity] ), ALL ( DimCustomer[CustomerLabel] ) ) )
Best Regards,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |