Forum Discussion
Rank and Lookupvalue
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
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...
2 Replies
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...