User Profile
yassine_mendy59
Frequent Visitor
Joined 2 years ago
User Widgets
Contributions
RANKING in a Matrix with hierarchies with data from 2 tables
Objective: Create a rank calculation in a matrix based on the number of customers. So, with a hierarchy: Region Store Department We have table A, which contains the store ID, department, and number of customers. We have table B, which contains the store ID, store label, and associated region label for each store ID. The desired matrix would be: Region (table B) Store (table B) Department (Table A) SUM Number of customers (Measure) Associated rank (Measure) The rank should therefore be calculated for each of the hierarchies based on the number of customers. At the service level, I have no problem with the calculation, but at the other levels (store and region), I do have some issues. At the store level, I have a calculation, but strangely, I will have the same rank for stores with 645, 636, and 623 customers, respectively. This is illogical. At the region level, I only have 1s. Actual measure : Adhesions_Rank = VAR rank_region = RANKX( ALL(B[region_label]), CALCULATE([NB_CUSTOMERS]), , DESC, DENSE ) VAR rank_magasin = RANKX( ALL(B[entity_label]), CALCULATE([[NB_CUSTOMERS]]), , DESC, DENSE ) VAR rank_service = RANKX( ALL(A[service_label]), CALCULATE([NB_CUSTOMERS]), , DESC, DENSE ) RETURN SWITCH( TRUE(), ISINSCOPE(A[service_label]), rank_service, ISINSCOPE(B[entity_label]), rank_magasin, ISINSCOPE(B[region_label]), rank_region )Solved1.3KViews2likes11CommentsCalcul of rank in the matrix for the hierarchies, for a range of dates
Hi everyone, This post follows a precedent one : https://community.fabric.microsoft.com/t5/Desktop/Calcul-of-rank-in-the-matrix-for-the-hierarchies/m-p/4074534 This is the same situation but the difference is that I have now informations for differents dates among 2022,2023, 2024. This is a subset of the table : Date Region City Department Number of products solds 01/08/2023 Nord Valenciennes A 12 102 01/08/2023 Nord Valenciennes B 10 090 01/08/2023 Nord Valenciennes C 23 456 01/08/2023 Sud Marseille A 11 118 01/08/2023 Sud Marseille B 13 456 01/08/2023 Sud Marseille C 9850 02/08/2023 Nord Valenciennes A 6050 02/08/2023 Nord Valenciennes B 10 083 02/08/2023 Nord Valenciennes C 23 452 02/08/2023 Sud Marseille A 9453 02/08/2023 Sud Marseille B 8250 02/08/2023 Sud Marseille C 4321 ... ... ... ... ... Matrix with 3 levels of hierarchy Region City Department The aim would be to have a different rank calculation for the matrix, depending on the hierarchy you're in: - Region level : Ranking between regions according to the number of products sold - City level: Ranking between cities according to the number of products sold per city - Department level: For the same department, I want the rank for the number of products sold for the same department between the different stores, Ex -> for department A, I want between stores for department A. I have three reports, to show this for different scales of date : - Cumulative Year-To-Date - Monthly - Date to Date The solution who was given to me in my previous report, works at Region and City Hierarchy, but the rank calculation doesn't work at the Department hierarchy level, I have some very strange ranks, who mean nothing. Thank you for your helpSolved623Views0likes4CommentsRe: Calcul of rank in the matrix for the hierarchies
Thank you, I get exactly the result i wanted, i have been dealing with the problem for a few days now, you save me. I juste have another question, what should I do if the informations in the TABLE were stored in 4 tables, because it is my original case, i have 3 dimensions tables for Region, City and Department, and one table of fact about the products solds. Should Necessarly create the table TABLE I created, or there is a way without created this table.717Views0likes1CommentRe: Calcul of rank in the matrix for the hierarchies
The problem I have is not about to switch rank, it is more about the calcul of the rank especially, for the rank fir same department, I want the rank for the number of products sold for the same department between the different stores. But i do not succed to calculate it, it only displays 1 for every lines752Views0likes0CommentsCalcul of rank in the matrix for the hierarchies
Situation Situation I have a table as follows : Region City Department Number of products solds Nord Valenciennes A 12 102 Nord Valenciennes B 10 090 Nord Valenciennes C 23 456 Sud Marseille A 11 118 Sud Marseille B 13 456 Sud Marseille C 28 934 Matrix with 3 levels of hierarchy Region City Department The aim would be to have a different rank calculation for the matrix, depending on the hierarchy you're in: - Region level : Ranking between regions according to the greatest number of products sold - City level: Ranking between cities according to the number of products sold per city - Department level: For the same department, I want the rank for the number of products sold for the same department between the different stores, Ex -> for department A, I want between stores for department A. Translated with DeepL.com (free version) Visual of desired MATRIX Region/ City / Department Rank Nord Valenciennes A B C 2 2 1 2 2 Sud Marseille A B C 1 1 2 1 1Solved822Views0likes5Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.