cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MCornish
Responsive Resident
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

 

DatePromotion ValueOrder ValueAll 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

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 ()
    )

Capture.PNG
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.

View solution in original post

@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

View solution in original post

4 REPLIES 4
v-frfei-msft
Community Support
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.

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

LinkColumnOrderNumLineNoProductLineCostQty
123456-11234561PROD01 £     1.991
123456-21234562PROD02 £     2.991
123456-31234563PROD03 £     4.501
123456-41234564PROD04 £     3.991

 

PromoLines

LinkColumnOrderNumLineNoPromoID
123456-11234561245
123456-21234562654

 

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

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 ()
    )

Capture.PNG
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.

View solution in original post

@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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors