cancel
Showing results for
Did you mean:
Responsive Resident

## Sum where exists in another table

Ok, its probably easier to show you what im trying to create rather than try explain it

 Date Promotion Value Order Value All Orders 01/01/2019 £                  952.00 £        975.00 £  2,431.00 02/01/2019 £                  708.00 £        731.00 £  2,829.00 03/01/2019 £                  201.00 £        224.00 £  3,926.00 04/01/2019 £                  799.00 £        822.00 £  2,335.00 05/01/2019 £                  267.00 £        290.00 £  3,394.00 06/01/2019 £                  196.00 £        219.00 £  2,903.00 07/01/2019 £                  550.00 £        573.00 £  3,830.00 08/01/2019 £                  544.00 £        567.00 £  2,784.00 09/01/2019 £                  418.00 £        441.00 £  2,744.00 10/01/2019 £                  555.00 £        578.00 £  3,765.00 11/01/2019 £                  696.00 £        719.00 £  3,553.00 12/01/2019 £                  739.00 £        762.00 £  2,264.00 13/01/2019 £                  987.00 £    1,010.00 £  3,825.00 14/01/2019 £                  720.00 £        743.00 £  3,336.00 15/01/2019 £                  316.00 £        339.00 £  3,866.00

So for the first we had £952 of discounted order lines. The value of the whole order sum'd would be £975

The model looks is standard order structure (Header, Lines) and lines is connected to a "PromoLines" table that holds order numbers (and the line) that is on promotion.

What I want to do is, If the order appears in the PromoLines table, sum ALL the corresponding lines in the Lines table

Cheers

2 ACCEPTED SOLUTIONS
Community Support

Hi @MCornish ,

Sorry for mu late respond, we can create a measure as below.

``````Measure =
VAR ordernum =
VALUES ( 'Table (2)'[OrderNum] )
RETURN
IF (
ISFILTERED ( 'Table (2)'[PromoID] ),
CALCULATE (
SUM ( 'Table'[LineCost] ),
FILTER (
'Table',
&& 'Table'[OrderNum] IN ordernum
)
),
BLANK ()
)
``````

Pbix as attached as well.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Responsive Resident

With a small tweak it does what I need.

``````Measure =
VAR ordernum =
VALUES ( 'Table (2)'[OrderNum] )
RETURN
IF (
ISFILTERED ( 'Table (2)'[PromoID] ),
CALCULATE (
SUM ( 'Table'[LineCost] ),
FILTER (
'Table',
'Table'[OrderNum] IN ordernum
)
),
BLANK ()
)``````

Basically I removed the

`'Table'[LinkColumn] IN linkcolumn`

from the SUM.

Cheers

4 REPLIES 4
Community Support

Hi @MCornish ,

Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Responsive Resident

The datasource is a PowerBI dataset containing confidential sales info. The est i can do is show you a mock up of the tables involved.

Lines

 LinkColumn OrderNum LineNo Product LineCost Qty 123456-1 123456 1 PROD01 £     1.99 1 123456-2 123456 2 PROD02 £     2.99 1 123456-3 123456 3 PROD03 £     4.50 1 123456-4 123456 4 PROD04 £     3.99 1

PromoLines

 LinkColumn OrderNum LineNo PromoID 123456-1 123456 1 245 123456-2 123456 2 654

So if I was looking at PromoID 245 the total for the promo is £1.99. But the Order value is £13.47 i.e. all the lines for that order.

PromoID 654 would return £2.99 and Order value is still £13.47

Hope this helps

Community Support

Hi @MCornish ,

Sorry for mu late respond, we can create a measure as below.

``````Measure =
VAR ordernum =
VALUES ( 'Table (2)'[OrderNum] )
RETURN
IF (
ISFILTERED ( 'Table (2)'[PromoID] ),
CALCULATE (
SUM ( 'Table'[LineCost] ),
FILTER (
'Table',
&& 'Table'[OrderNum] IN ordernum
)
),
BLANK ()
)
``````

Pbix as attached as well.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Responsive Resident

With a small tweak it does what I need.

``````Measure =
VAR ordernum =
VALUES ( 'Table (2)'[OrderNum] )
RETURN
IF (
ISFILTERED ( 'Table (2)'[PromoID] ),
CALCULATE (
SUM ( 'Table'[LineCost] ),
FILTER (
'Table',
'Table'[OrderNum] IN ordernum
)
),
BLANK ()
)``````

Basically I removed the

`'Table'[LinkColumn] IN linkcolumn`

from the SUM.

Cheers

Announcements