Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mema
Frequent Visitor

Greatest difference

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

8 REPLIES 8
wlinley
Advocate I
Advocate I

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?

Mema
Frequent Visitor

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

dax
Community Support
Community Support

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.

Mema
Frequent Visitor

Sample of my Data

PeriodManageCodeKeyActivityCodeKeyItemCodeKeyOutturnForecastOutturn DifOutturnDif RankManageActivityItem
32763422224922432-1,229.090.001268282763422224922432
42763422224922432-1,229.090.001268282763422224922432
52763422224922432-1,229.090.001268282763422224922432
62763422224922432-1,229.090.001268282763422224922432
72763422224922432-3,415.592,186.50440122763422224922432
82763422224922432-4,644.681,229.09559462763422224922432
92763422224922432-4,644.680.001268282763422224922432
102763422224922432-4,644.680.001268282763422224922432
112763422224922432-4,644.680.001268282763422224922432
122763422224922432-4,644.680.001268282763422224922432
42763921345621775402.520.001268282763921345621775
52763921345621775465.9363.411112992763921345621775
62763921345621775465.930.001268282763921345621775
72763921345621775473.007.071240632763921345621775
82763921345621775788.32315.32854952763921345621775
92763921345621775788.320.001268282763921345621775
102763921345621775788.320.001268282763921345621775
1127639213456217751,799.111,010.79600942763921345621775
1227639213456217751,799.110.001268282763921345621775
42763921345622107191.390.001268282763921345622107
52763921345622107222.7831.391176612763921345622107
6276392134562210710,263.5210,040.74183822763921345622107
7276392134562210713,869.783,606.26338612763921345622107
8276392134562210713,869.780.001268282763921345622107
9276392134562210715,791.731,921.95466942763921345622107
10276392134562210716,148.40356.67831172763921345622107
11276392134562210716,620.47472.07771152763921345622107
12276392134562210716,970.47350.00834892763921345622107
32763921345622528-5,779.010.001268282763921345622528
42763921345622528-5,779.010.001268282763921345622528
52763921345622528-5,779.010.001268282763921345622528
62763921345622528-5,779.010.001268282763921345622528
727639213456225283,207.998,987.00196852763921345622528
827639213456225283,207.990.001268282763921345622528
927639213456225284,457.991,250.00555642763921345622528
1027639213456225284,457.990.001268282763921345622528
11276392134562252825,986.9721,528.98117872763921345622528
12276392134562252845,765.9919,779.02124332763921345622528

The rank that I can get to work is the one over the whole table 

OutturnDif Rank =
    RANKX(
        CombinedData,
        CombinedData[Outturn Dif],
        ,
        DESC,
        Dense
        )
I would like to be able to show is which activity item combination has the biggest difference for each ManageCode or which ones have had the biggest difference for a certain period.
 
Thanks for your help, I hope this helps make sense of what I am trying to achieve
dax
Community Support
Community Support

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.

 

 

Mema
Frequent Visitor

 you for your response, I get the error EARLIER/EARLIEST refers to an earlier row context which doesn't exist

dax
Community Support
Community Support

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.

 

Mema
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.