Most efficient way to identify and multiply

[BIanon]

Hello Community,

I have a table which looks like this(dummy data):

TripId	PurchaseId	UserId	Value	Factor
10001	1001	5000	156	2,3
10001	1002	5000	165	2,3
10001	1003	5000	165	2,3
10001	1004	5000	132	2,3
10002	1005	5001	111	1,8
10002	1006	5001	98	1,8
10002	1007	5001	64	1,8
10002	1008	5001	154	1,8
10002	1009	5001	55	1,8

The real table also contains more data points than just 'Value'.

For every calculation I need to multiply any value by the 'Factor' value.

Currently I am using a measure like this:

SUMX('Table', 'Table'[Value] * 'Table'[Factor])

I am searching for a more efficient way to achieve this.

I need to have the 'raw' data and pre-calculating these fields would bloat my model past the 1 GB limit unfortunately.

I also have a different situation where I need to sum the Factor column but only include the value once per TripId which I am currently achieving like this:

SUMX(VALUES('Table'[TripId]), CALCULATE(MAX('Table'[Factor])))

iI am experincing particularly slow performance with this last one and am wondering if there is a more efficient way to arrive at the factor as a scalar

[amitchandak]

@BIanon , Both seem correct

If file permits create a column in power query or dax

'Table'[Value] * 'Table'[Factor]

and then do simple sum

[BIanon]

Thank you for your response.

Unfortunately this bloats my model a lot and adding factored columns like this while keeping the raw data (which I need to keep) would push me past the 1 GB limit.