Aggregation over LOOKUPVALUES

[pbi_m1]

Hi,

I wonder how to best solve the following problem: There are 2 tables:

table A with columns: DATE (date), GROUP (string), ID (int), PRICE(float)

table B with columns: ID (int), FACTOR (float)

FACTOR is a date-independent but ID specific number. PRICE is a date-specific and ID-specific number. GROUP is a grouping over IDs. The IDs in both tables can be used to join the tables i.e. A.ID = B.ID 

I'd like to create a 'Table' Visualisation in Power BI to show the following columns for a DATE that is set by a slicer:

GROUP, SUM(FACTOR for all IDs in this GROUP), SUM( PRICE * FACTOR for all IDs in this GROUP) 

I have managed to build the above by creating a new column in table A FACTOR = LOOKUPVALUES(B[FACTOR], B[ID], A[ID]) I wondered if there is a way around this extra lookup since in reality I have like 5 'FACTOR' columns and a lot of DATEs which creates a lot of data.

[Greg_Deckler]

That's lots of words. Sample data with an example of what you want as output is much easier to understand. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[pbi_m1 · Accepted solution]

Hi Greg, thanks for the link. I guess my databases was big so I thought describing a simplified version of it would be easier to understand. After another hour of googling I found I think the better way that I was looking for which is (column-by-column) in the 'Table' visualisation:

GROUP,

Measure1 = SUMX( A, RELATED(B[FACTOR]) ),

Measure2 = SUMX( A, A[PRICE] * RELATED(B[FACTOR]))