Rank and Lookupvalue

[Anonymous]

Hello, I would appreciate your help with the following question:

I am trying to write DAX formula which would return number in the last column in below table. This is nth product's Volume sales where nth position is defined on Value rank: for example Product 1 is 6th in Value sales and as a result I want Volume Sales of 6th product ranked by Volume (both ranks are calculated with RANKX).

I am stuck with virtual tabels created by ADDCOLUMNS but I cannot run LOOKUPVALUE thru them.

Thank you in advance for your support.

Luka

[lbendlin · Accepted solution]

Ok, I think this is one of the craziest formulas I have ever written.  Use at your own risk

VOL Sales based on VAL rank := 
var valr = rankx(all(Products[Product]),CALCULATE(sum(Products[Value Sales])))
var v = ADDCOLUMNS(ALL(Products[Product]),"sales",var p = Products[Product] return calculate(sum(Products[Volume Sales]),all(Products),Products[Product]=p))
var v2 = ADDCOLUMNS(v,"volr",var s=[sales] return CALCULATE(RANK.EQ(s,Products[Volume Sales]),all(Products)))
return CONCATENATEX(filter(v2,[volr]=valr),format([sales],"#"),"")

DAXformatter.com treatment :

VOL Sales based on VAL rank =
VAR valr =
    RANKX ( ALL ( Products[Product] ), CALCULATE ( SUM ( Products[Value Sales] ) ) )
VAR v =
    ADDCOLUMNS (
        ALL ( Products[Product] ),
        "sales",
            VAR p = Products[Product]
            RETURN
                CALCULATE (
                    SUM ( Products[Volume Sales] ),
                    ALL ( Products ),
                    Products[Product] = p
                )
    )
VAR v2 =
    ADDCOLUMNS (
        v,
        "volr",
            VAR s = [sales]
            RETURN
                CALCULATE ( RANK.EQ ( s, Products[Volume Sales] ), ALL ( Products ) )
    )
RETURN
    CONCATENATEX ( FILTER ( v2, [volr] = valr ), FORMAT ( [sales], "#" ), "" )

I leave the Totals as an exercise for you...

[Anonymous]

It sure is crazy but it works 😀

Thank you for your support.

Best regards,

Luka