Question about the MAXX

[VSIM]

Hello. I have similar table:
I want to find the max difference between rows in column "Numbers" and corresponding letter in "Letters" to show them in 2 KPI visuals in the reports (as a result, I mean to get "F" and 8, as the maximum difference from the numbers rows is between 20 and 12, and not 5-2, or 9-5, or 6-9, or 12-6....). Can anybody tell me how to write the measure for that? Thanks in advance

 

[daxer-almighty]

This is not clear. What happens when somebody filters the table by letters: A, D, H? What result do you expect?

[VSIM]

 After filter A, D, H, the result will be "H" and 25-6 = "19", as 19 is more then (D-A )   6-2="4". Actually, I have incremental date column instead of letters column in reality. I put the table with letters here for simpicity (with my opinion).

[Anonymous]

// Let's call the table T.

[Max Diff Value] =
// It does not matter whether
// the Letters column stores
// letters or numbers or dates
// as long as the entries may
// be sorted.
var __lettersWithDiffs =
    GENERATE(
        T,
        var __currentLetter = T[Letter]
        var __currentNumber = T[Number]
        // can be BLANK if we're on the first
        // letter as seen in the current
        // context
        var __prevLetter =
            MAXX(
                filter(
                    T,
                    T[Letter] < __currentLetter
                ),
                T[Letter]
            )
        // can be BLANK if we're on the first
        // letter as seen in the current
        // context
        var __prevNumber =
            MAXX(
                filter(
                    T,
                    T[Letter] = __prevLetter
                ),
                T[Number]
            )
        var __diff =
            __currentNumber - __prevNumber
        RETURN
            row(
                "@PrevLetter", __prevLetter,
                "@Diff", __diff
            )
    )
var __maxDiff =
    MAXX(
        __lettersWithDiffs,
        [@Diff]
    )
RETURN
    __maxDiff

and the first letter where the max diff is found:

[Max Diff Letter] =
// It does not matter whether
// the Letters column stores
// letters or numbers or dates
// as long as the entries may
// be sorted.
var __lettersWithDiffs =
    GENERATE(
        T,
        var __currentLetter = T[Letter]
        var __currentNumber = T[Number]
        // can be BLANK if we're on the first
        // letter as seen in the current
        // context
        var __prevLetter =
            MAXX(
                filter(
                    T,
                    T[Letter] < __currentLetter
                ),
                T[Letter]
            )
        // can be BLANK if we're on the first
        // letter as seen in the current
        // context
        var __prevNumber =
            MAXX(
                filter(
                    T,
                    T[Letter] = __prevLetter
                ),
                T[Number]
            )
        var __diff =
            __currentNumber - __prevNumber
        RETURN
            row(
                "@PrevLetter", __prevLetter,
                "@Diff", __diff
            )
    )
var __maxDiffLetter =
    MAXX(
        TOPN(1,
            __lettersWithDiffs,
            // this sorts by the differences
            // in a descending order
            [@Diff],
            DESC,
            // and this sorts within the
            // same differences by letters
            // in an ascending order
            T[Letter],
            ASC
        ),
        T[Letter]
    )
RETURN
    __maxDiffLetter

[Anonymous]

Of course, the stuff above works correctly but you should understand that if you put the measures in a table and then drop the letters onto rows, they will be calculating values for THIS ROW ONLY. If you want to get these measures calculated on a selection of rows, then you have to put the measures onto a card or check the measures' values on the Total row in a table or in a matrix.