Forum Discussion

gkir's avatar
gkir
Icon for Advocate II rankAdvocate II
4 years ago

DAX. Find value based on rankx

Hello,

 

Can someone help me with Dax?

I have a table with values. Each item every day has no more than 1 value:

 

I need to calculate rolling 3 year median for every item. The problem is that the median which I need is calculated not arithmetically, but negative values should be the worst, then the large positives and the best values are small positives.

 

So, first of all, I need to rank values during 3 years period for each item and find the middle number.

I tried to use RANKX formula but I have no idea where should I put the restriction about 3 years and something more is wrong because it's not calculating correctly even for all period:

 

 

Need Help

9 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Can you please provide sample non-confidential data? (Not as an image)

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Thanks for the data sample. Can you please explain how you wish to calculate the median? I don't quite understand the statement reading

    "The problem is that the median which I need is calculated not arithmetically, but negative values should be the worst, then the large positives and the best values are small positives"

    Maybe if you provide a practical example?

    • gkir's avatar
      gkir
      Icon for Advocate II rankAdvocate II
      4 years ago

      For example:
      Item A has values:
      -2
      3
      6
      8
      9

      Arithmetical median would be: 6.

      but we want to rank like this:
      -2
      9
      8
      6
      3

      So our median is 8.


  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Thanks. I take it that if there are more negative values, you want them ranked in descending order also?

    -1

    -2

    9

    8

    6

    3

    Do you need it down to date level? So it would be the median for all dates in the last 3 years <= to the current date in the visual?

    • gkir's avatar
      gkir
      Icon for Advocate II rankAdvocate II
      4 years ago

      Thank you for your replies!

      I need negative numbers ranked in ascending order. For example: 
      -15
      -2
      9
      8
      6
      3

      Median: 8,5.

      Yes, I need for every day calculate median for all dates in the last 3 years <= to the current date.
      For example for July 18th, 2022 we need rank numbers from July 19th, 2019 to July 18th, 2022.
      For July 15th, 2022 rank all numbers from July15th, 2019 to  July 15th, 2022.
      And all these medians should be calculated for each item individually.

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Ok, this is turning out to be quite a challenge for my scope of DAX. Here is the progress I've made so far.

        To test the measures, I've opted for creating a filter date to see the physical rendering of each measure. This means there are some filter expressions in the measures simply to get rid of blank rows.

        So here goes

        First, the model (including a separate date table to limit the rows in the visual. For this exercize I'm looking at a 1 year range:

        Now the measures step by step:

        Sum Value = SUM(FactTable[Value])

        Ascending rank for rows with negative values:

        Rank -ves =
VAR _mindate =
    CALCULATE (
        MIN ( 'Date Sel'[Sel Date] ),
        DATEADD ( 'Date Sel'[Sel Date], -1, YEAR )
    )
VAR _DatesTable =
    CALCULATETABLE (
        ALLSELECTED ( 'Date Table'[Date] ),
        DATESBETWEEN ( 'Date Table'[Date], _mindate + 1, MAX ( 'Date Sel'[Sel Date] ) )
    )
VAR _Rows =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Date Table'[Date] ),
            FILTER (
                'Date Table',
                'Date Table'[Date] > _mindate
                    && 'Date Table'[Date] <= MAX ( 'Date Sel'[Sel Date] )
            )
        )
    )
RETURN
    IF (
        AND ( _Rows >= 1, [Sum Value] < 0 ),
        RANKX ( _DatesTable, [Sum Value],, ASC, DENSE )
    )

        Number of rows with negative values:

        MAX _ve Rows = 
MAXX(ALL('Date Table'[Date]), [Rank -ves])

        Descending Rank of positive values

        Rank +ves =
VAR _mindate =
    CALCULATE (
        MIN ( 'Date Sel'[Sel Date] ),
        DATEADD ( 'Date Sel'[Sel Date], -1, YEAR )
    )
VAR _DatesTable =
    CALCULATETABLE (
        ALLSELECTED ( 'Date Table'[Date] ),
        DATESBETWEEN ( 'Date Table'[Date], _mindate + 1, MAX ( 'Date Sel'[Sel Date] ) )
    )
VAR _Rows =
    COUNTROWS (
        CALCULATETABLE (
            VALUES ( 'Date Table'[Date] ),
            FILTER (
                'Date Table',
                'Date Table'[Date] > _mindate
                    && 'Date Table'[Date] <= MAX ( 'Date Sel'[Sel Date] )
            )
        )
    )
RETURN
    IF (
        AND ( _Rows >= 1, [Sum Value] > 0 ),
        RANKX ( _DatesTable, [Sum Value],, DESC, DENSE ) + [MAX _ve Rows]
    )

        Final Rank of all rows by Item

        All Rank Vals = 
[Rank -ves] + [Rank +ves]

        Median calculation

        MEDIAN =
VAR _Table =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( FactTable, NOT ( ISBLANK ( ( [All Rank Vals] ) ) ) ),
            'Dim Item'[dItem],
            'Date Table'[Date]
        ),
        "@RANK", [All Rank Vals]
    )
VAR _Median =
    ROUND ( MEDIANX ( _Table, [@RANK] ), 0 )
RETURN
    _Median

        FInal Value based on Median:

        Final Median Value =
VAR _FinalMed =
    ROUND ( CALCULATE ( [MEDIAN], REMOVEFILTERS ( 'Date Table'[Date] ) ), 0 )
RETURN
    AVERAGEX (
        ALLSELECTED ( 'Date Table'[Date] ),
        CALCULATE ( IF ( [All Rank Vals] = _FinalMed, [Sum Value] ) )
    )

        To get:

        I've attached the sample PBIX file

        Apologies for the delay, but as I say, it's quite a challenge. I'll keep at it, but can't promise anythig at this stage...