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 that has 3 columns that make up a unique identifier, I have combined these in a new column. I have been able to do the difference for each one of these for each period in the financial year but I want to show which of these combinations had the greatest change each financial period and which period had the most movement.
Thanks for any help
Hi @Mema
I'm not sure what the question is here.
Can you advised what you have tried and what you are having a problem with?
I am asking how I can show the top 5 ranked differences, I have tried using Rankx in a calculated column, then using top n as a filter. I have tried to create a rankx measure, I can't get that to work at all, I have tried a filter on the measure that is rank less than or equal to 5
Hi Mema,
You said you can't make the rankx work in report, which problem did you get? And what is rankx difference? In addition, if possible, could you please inform me more detailed information (such as your sample data and your expected output)? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sample of my Data
Period | ManageCodeKey | ActivityCodeKey | ItemCodeKey | OutturnForecast | Outturn Dif | OutturnDif Rank | ManageActivityItem |
3 | 27634 | 222249 | 22432 | -1,229.09 | 0.00 | 126828 | 2763422224922432 |
4 | 27634 | 222249 | 22432 | -1,229.09 | 0.00 | 126828 | 2763422224922432 |
5 | 27634 | 222249 | 22432 | -1,229.09 | 0.00 | 126828 | 2763422224922432 |
6 | 27634 | 222249 | 22432 | -1,229.09 | 0.00 | 126828 | 2763422224922432 |
7 | 27634 | 222249 | 22432 | -3,415.59 | 2,186.50 | 44012 | 2763422224922432 |
8 | 27634 | 222249 | 22432 | -4,644.68 | 1,229.09 | 55946 | 2763422224922432 |
9 | 27634 | 222249 | 22432 | -4,644.68 | 0.00 | 126828 | 2763422224922432 |
10 | 27634 | 222249 | 22432 | -4,644.68 | 0.00 | 126828 | 2763422224922432 |
11 | 27634 | 222249 | 22432 | -4,644.68 | 0.00 | 126828 | 2763422224922432 |
12 | 27634 | 222249 | 22432 | -4,644.68 | 0.00 | 126828 | 2763422224922432 |
4 | 27639 | 213456 | 21775 | 402.52 | 0.00 | 126828 | 2763921345621775 |
5 | 27639 | 213456 | 21775 | 465.93 | 63.41 | 111299 | 2763921345621775 |
6 | 27639 | 213456 | 21775 | 465.93 | 0.00 | 126828 | 2763921345621775 |
7 | 27639 | 213456 | 21775 | 473.00 | 7.07 | 124063 | 2763921345621775 |
8 | 27639 | 213456 | 21775 | 788.32 | 315.32 | 85495 | 2763921345621775 |
9 | 27639 | 213456 | 21775 | 788.32 | 0.00 | 126828 | 2763921345621775 |
10 | 27639 | 213456 | 21775 | 788.32 | 0.00 | 126828 | 2763921345621775 |
11 | 27639 | 213456 | 21775 | 1,799.11 | 1,010.79 | 60094 | 2763921345621775 |
12 | 27639 | 213456 | 21775 | 1,799.11 | 0.00 | 126828 | 2763921345621775 |
4 | 27639 | 213456 | 22107 | 191.39 | 0.00 | 126828 | 2763921345622107 |
5 | 27639 | 213456 | 22107 | 222.78 | 31.39 | 117661 | 2763921345622107 |
6 | 27639 | 213456 | 22107 | 10,263.52 | 10,040.74 | 18382 | 2763921345622107 |
7 | 27639 | 213456 | 22107 | 13,869.78 | 3,606.26 | 33861 | 2763921345622107 |
8 | 27639 | 213456 | 22107 | 13,869.78 | 0.00 | 126828 | 2763921345622107 |
9 | 27639 | 213456 | 22107 | 15,791.73 | 1,921.95 | 46694 | 2763921345622107 |
10 | 27639 | 213456 | 22107 | 16,148.40 | 356.67 | 83117 | 2763921345622107 |
11 | 27639 | 213456 | 22107 | 16,620.47 | 472.07 | 77115 | 2763921345622107 |
12 | 27639 | 213456 | 22107 | 16,970.47 | 350.00 | 83489 | 2763921345622107 |
3 | 27639 | 213456 | 22528 | -5,779.01 | 0.00 | 126828 | 2763921345622528 |
4 | 27639 | 213456 | 22528 | -5,779.01 | 0.00 | 126828 | 2763921345622528 |
5 | 27639 | 213456 | 22528 | -5,779.01 | 0.00 | 126828 | 2763921345622528 |
6 | 27639 | 213456 | 22528 | -5,779.01 | 0.00 | 126828 | 2763921345622528 |
7 | 27639 | 213456 | 22528 | 3,207.99 | 8,987.00 | 19685 | 2763921345622528 |
8 | 27639 | 213456 | 22528 | 3,207.99 | 0.00 | 126828 | 2763921345622528 |
9 | 27639 | 213456 | 22528 | 4,457.99 | 1,250.00 | 55564 | 2763921345622528 |
10 | 27639 | 213456 | 22528 | 4,457.99 | 0.00 | 126828 | 2763921345622528 |
11 | 27639 | 213456 | 22528 | 25,986.97 | 21,528.98 | 11787 | 2763921345622528 |
12 | 27639 | 213456 | 22528 | 45,765.99 | 19,779.02 | 12433 | 2763921345622528 |
The rank that I can get to work is the one over the whole table
Hi Mema,
If you want to rank based on ManageCode, you could try to use below calculated column
OutturnDif Rank1 =
RANKX (
FILTER (
CombinedData,
CombinedData[ManageCodeKey] = EARLIER ( CombinedData[ManageCodeKey] )
),
CombinedData[Outturn Dif],
,
ASC,
DENSE
)
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
you for your response, I get the error EARLIER/EARLIEST refers to an earlier row context which doesn't exist
Hi Mema,
Above expression is used on calculated column, not in measure.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I have tried it as a column and I get
A Single value for column 'ManageCodeKey' in the table 'CombinedData' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |