Forum Discussion
Aggregation over LOOKUPVALUES
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.
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]))
2 Replies
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
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]))
