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.
Hi
I need to be help create a Rank column. I need to be able to generate the rank in the context of the Time Period for the Products based on the Amount and This rank will be in the context of the Market where the market acts as a slicer. I added the sample data and the rank. The DAX I created always gives me a rank 1.
Markets | Product | Time Period | Amt | Rank |
North | A | Last 52 Weeks | 1 | 1 |
North | B | Last 52 Weeks | 2 | 2 |
North | C | Last 52 Weeks | 3 | 3 |
North | D | Last 52 Weeks | 4 | 4 |
North | E | Last 52 Weeks | 5 | 5 |
North | F | Last 52 Weeks | 6 | 6 |
North | A | Last 13 Week | 11 | 5 |
North | B | Last 13 Week | 12 | 4 |
North | C | Last 13 Week | 4 | 6 |
North | D | Last 13 Week | 324 | 2 |
North | E | Last 13 Week | 32 | 3 |
North | F | Last 13 Week | 3434 | 1 |
North | A | Last 26 Weeks | 10 | 6 |
North | B | Last 26 Weeks | 34 | 4 |
North | C | Last 26 Weeks | 13 | 5 |
North | D | Last 26 Weeks | 432432 | 1 |
North | E | Last 26 Weeks | 3423 | 2 |
North | F | Last 26 Weeks | 124 | 3 |
North | A | Last 4 Weeks | 1 | 5 |
North | B | Last 4 Weeks | 4 | 2 |
North | C | Last 4 Weeks | 7 | 1 |
North | D | Last 4 Weeks | 2 | 4 |
North | E | Last 4 Weeks | 3 | 3 |
North | F | Last 4 Weeks | 0 | 6 |
Any Help is appreciated.
Hello All,,
My requirement is to give rank values to last 12 months dynamically And we follow June of every month as our finnacial year. Can anyone please help with the rank function measure to create calculated column
It would be really helpfull thanks
So, are you OK if this is a measure? I have had far more luck with RANKX as a measure versus as a column. You could create two measures:
MySum = SUM(Table[Amt]) MyRank = RANKX(ALL(Table),[MySum])
You can create a column like this:
Column = RANKX(aRanks,[Amt])
But it won't be grouped like you want. Here is one of the better articles explaining RANKX:
https://www.wiseowl.co.uk/blog/s2469/rankx-function.htm
GIve this MEASURE a shot as well
RANK = RANKX ( FILTER ( ALLSELECTED ( Table1 ), Table1[Time Period] = SELECTEDVALUE ( Table1[Time Period] ) ), CALCULATE ( SUM ( Table1[Amt] ) ), , DESC, DENSE )
Infact this one should give the proper results with slicers
RANK = RANKX ( CALCULATETABLE ( VALUES ( Table1[Product] ), FILTER ( ALLSELECTED ( Table1 ), Table1[Time Period] = SELECTEDVALUE ( Table1[Time Period] ) ) ), CALCULATE ( SUM ( Table1[Amt] ) ), , DESC, DENSE )
Thank you for providing the information but the DAX you provided does not give me the correct result, it always gives rank as 1
HI @rampsaladi
See the attached file here with your sample data
yes I can see that but It does not work when I do it but let me try again. I also have another requirement where I need to be able to display the Rank for each time period side by side and I tried to do that by filterting the data for the specific time period but it did not work. Any help is appreciated.
Basically I need to show a side by side comparison of the Product Rank over time period in a specific market
Hi @rampsaladi
to show a side by side comparison of the Product Rank over time period in a specific market.....
You can try using this rank MEASURE
RANK_ = RANKX ( ALLSELECTED ( Table1[Product] ), CALCULATE ( SUM ( Table1[Amt] ) ), , DESC, DENSE )
Use a Matrix Table...Put Time Period in Columns..Products in Rows and RANK measure in values
This is really helpful you know I never thought of it, but I have one more request can I actually have a different rank measure for each Time Period, the only reason i ask it I have other measure values that I need to dsiplay along with Rank and if I add them its get little bit skewed. the look. So Ideally I would like to have a Rank Measure for Say latest 13 Weeks timeperiod data and this will change based on the Market Slicer
Hi @rampsaladi
In that case try his pattern
RANK_Last 13 Week = RANKX ( ALLSELECTED ( Table1[Product] ), CALCULATE ( SUM ( Table1[Amt] ), Table1[Time Period] = "Last 13 Week" ), , DESC, DENSE )
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 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |