Forum Discussion
adding / sum lookupvalue
Hello Everyone!
Quick question is there a way to add up values that are coming from a measure that uses lookupvalue?
Here's the measure:
lookupvalue = LOOKUPVALUE(Report[SUM_SERVICE_MONTHLY_FEE_HUF];Report[Custom_date]; [TOP2]; Report[LINK_CODE]; [link_select] )
Basically the [link_select] is just the link code, I dunno I just put it there just to be sure. (Link code is a key-like column)
and it uses this measure:
TOP2 = CALCULATE( FIRSTNONBLANK(TOPN(2;CALCULATETABLE(VALUES(Report[Custom_date]);DATESBETWEEN(Report[Custom_date];BLANK();SELECTEDVALUE(Report[Custom_date];LASTDATE(Report[Custom_date]))); GROUPBY(Report; Report[LINK_CODE]));Report[Custom_date]); 0))
[Custom_date] is a date column that I created using 3 date columns. It can contain duplikates.
Basically I need a measure/column whichever fits, to get the previous date data (e.g sum fee, how many distinct link code and other summed products). I wrote sum, but I only need it when the date is agregated to months or groups.
Thank you :)
Viki
3 Replies
So typically, you use VAR to create a temp table using SUMMARIZE or ADDCOLUMNS and then feed that into a SUMX or something along those lines. See the basic technique here:
https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
Thank you :)
I have already tried this one, but ones again like this:
sumx = SUMX(SUMMARIZE(Report; Report[LINK_CODE];Report[Custom_date]; "look"; [lookupvalue]);[look])
sumx = SUMX(ADDCOLUMNS(Report; "look"; [lookupvalue]; "link"; Report[LINK_CODE]; "date"; Report[Custom_date]);[look])and also when I tied to make a separate table I get this error:
A table of multiple values was supplied where a single value was expected
Hi lattys,
As I know, it is not able to use a LOOKUPVALUE function in measure formula. From above measure formulas, I cannot understand what you were trying to achieve, would you please share some sample data and show us your desired result?
Regards,
Yuliana Gu
