Forum Discussion

Mema's avatar
Mema
Frequent Visitor
6 years ago

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...
Need Help
Tutorial Requests
dax's avatar
dax
Community Support
6 years ago

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's avatar
Mema
Frequent Visitor
6 years ago

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's avatar
    dax
    Community Support
    6 years ago

    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's avatar
      Mema
      Frequent Visitor
      6 years ago

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

      • dax's avatar
        dax
        Community Support
        6 years ago

        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.