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.
I have a table with data like this:
StoreID | Week/End | Sales =========================== 1 | 9/2 | 10 2 | 9/2 | 5 3 | 9/2 | 3 1 | 9/9 | 5 2 | 9/9 | 2 3 | 9/9 | 10
What I need to do is rank the sales by week, then add up the value of the ranking.
I tried a measure using RANKX like this:
=RANKX(ALL(Stores), SUMX(RELATEDTABLE(Sales), [Sales]))
Which will give me the following table:
StoreID | Week/End | Sales | Rank ===================================== 1 | 9/2 | 10 | 1 2 | 9/2 | 5 | 2 3 | 9/2 | 3 | 3 1 | 9/9 | 5 | 2 2 | 9/9 | 2 | 3 3 | 9/9 | 10 | 1
What I need to do now is to add up the rank column by Store, so in the end I would get a table like this:
StoreID | Overall Rank ======================= 1 | 3 2 | 5 3 | 4
So store 1 would have a value of 3, because of the w/e 9/2 of 1 and a w/e 9/9 rank of 2. store 2 would have a overall rank of 5, because of the w/e 9/2 of 2 and a w/e 9/9 rank of 3, and so on.
I would try to do this in SQL, but unfortunately some of my base data is in excel and some of it is in SQL, so I need to do this in DAX or M.
Any ideas would be greatly appreciated!
Solved! Go to Solution.
Try to create a measure as below. See more details in the attached pbix file.
Overall Rank = VAR tbl = ADDCOLUMNS ( sales, "rank", RANKX ( CALCULATETABLE ( sales, ALLEXCEPT ( sales, sales[Week/End] ) ), sales[Sales], , DESC, DENSE ) ) RETURN SUMX ( tbl, [rank] )
Try to create a measure as below. See more details in the attached pbix file.
Overall Rank = VAR tbl = ADDCOLUMNS ( sales, "rank", RANKX ( CALCULATETABLE ( sales, ALLEXCEPT ( sales, sales[Week/End] ) ), sales[Sales], , DESC, DENSE ) ) RETURN SUMX ( tbl, [rank] )
Hi,
A simple SUM measure should work
=SUM([Rank])
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |