Sum where exists in another table

[MCornish]

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

[v-frfei-msft · Accepted solution]

Hi @MCornish ,

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

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

Pbix as attached as well.

[v-frfei-msft]

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.

[MCornish]

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

[v-frfei-msft · Accepted solution]

Hi @MCornish ,

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

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

Pbix as attached as well.

[MCornish · Accepted solution]

@v-frfei-msft 

With a small tweak it does what I need.

Measure = 
VAR linkcolumn =
    VALUES ( 'Table (2)'[LinkColumn] )
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