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,
I have the following table for stores:
Date City Store Store Name Store Code Color Score Rank
1/1/2021 A XX downtown 01 Orange 20 1
1/1/2021 A XX downtown 01 Blue 19 2
1/1/2021 A XY midtown 02 Orange 18 1
1/1/2021 A XY midtown 02 Green 16 2
2/1/2021 B YZ center 03 Green 18 1
2/1/2021 B YZ center 03 Rose 17 2
...
3/1/2022 B YZ center 03 Green 20 1
4/1/2022 C ZQ park 04 Blue 19 1
The date is always referring to the first day of the month.
How to calculate a custom column (Rank), ranking the Colors according to their score in an specific Store name, in the same month?
The desired column is given
Thanks
Solved! Go to Solution.
Try this calculated column:
Rank =
VAR vDate = Table1[Date]
VAR vStore = Table1[Store]
VAR vTable =
FILTER ( Table1, Table1[Date] = vDate && Table1[Store] = vStore )
VAR vResult =
RANKX ( vTable, Table1[Score],, DESC, DENSE )
RETURN
vResult
Proud to be a Super User!
Thank you @DataInsights !
The following calculated column also worked for me:
RANKX(FILTER(Table1,Table1[Store Name]=EARLIER(Table1[Store Name]) && Table1[Date]=EARLIER(Table1[Date])),Table1[Score])
@DataInsights I have a last question related with the previous case, given the table and rank:
Date City Store Store Name Store Code Color Score Rank
1/1/2021 A XX downtown 01 Orange 20 1
1/1/2021 A XX downtown 01 Blue 19 2
1/1/2021 A XY midtown 02 Orange 18 1
1/1/2021 A XY midtown 02 Green 16 2
2/1/2021 B YZ center 03 Green 18 1
2/1/2021 B YZ center 03 Rose 17 2
...
3/1/2022 B YZ center 03 Green 20 1
4/1/2022 C ZQ park 04 Blue 19 1
How to calculate a measure (new ranking) to determine that in January 2021, because Orange counted 2 times with the highest score (20 in downtown, 18 in midtown), then orange is ranked as no. 1? other color could have counted 1 time in with the higher score in 1 store (so it is ranked as 2).
Thanks,
Try this calculated column:
Rank =
VAR vDate = Table1[Date]
VAR vStore = Table1[Store]
VAR vTable =
FILTER ( Table1, Table1[Date] = vDate && Table1[Store] = vStore )
VAR vResult =
RANKX ( vTable, Table1[Score],, DESC, DENSE )
RETURN
vResult
Proud to be a Super User!
Thanks @DataInsights, the calculated column provided works, but I need is a measure to be display as a number, having Year, Month selected in a slicer.
Thank you @DataInsights !
The following calculated column also worked for me:
RANKX(FILTER(Table1,Table1[Store Name]=EARLIER(Table1[Store Name]) && Table1[Date]=EARLIER(Table1[Date])),Table1[Score])
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 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |