Sum from another non related (can't link) table

[Leroynz]

Hi,

I have two tables: Table A has column "Booking #" and column "Method", Table A Booking # has multiple entries but each entry will only have one Method e.g email, web. Table B has column "Booking ID" and column "Lost Savings". Table B Booking ID contains mulitple entries of differing amounts with the same ID. In both tables the Booking # and Booking ID represent the same thing however I can't link the tables as when I do it comes up with mulitple entries error and won't link.

I want to be able to show the Lost Savings per method but the values all show as the total amount. Any help would be appreciated.

Row LabelsSum of Lost SavingsGrand Total$51,322.64

Method
(blank)	$51,322.64
After Hours	$51,322.64
E mail	$51,322.64
Fax	$51,322.64
Method	$51,322.64
Mixed Web	$51,322.64
Telephone	$51,322.64
Web	$51,322.64
Web Request	$51,322.64

Row LabelsSum of Lost SavingsGrand Total$51,322.64

[v-shex-msft · Accepted solution]

Hi @Leroynz,

For tables which not contains relationship, you need to manually get current item as condition to filter with row contents, otherwise they will return static value.

Sample measure:

Lost SavingsGrand =
VAR Booklist =
    CALCULATETABLE ( VALUES ( TableA[Booking #] ), VALUES ( TableA[Method] ) )
RETURN
    CALCULATE (
        SUM ( TableB[Lost Savings] ),
        FILTER ( ALL ( TableB ), [Booking ID] IN Booklist )
    )

Then use method column and above measure to create table visual.

Regards,

Xiaoxin Sheng

[v-shex-msft · Accepted solution]

Hi @Leroynz,

For tables which not contains relationship, you need to manually get current item as condition to filter with row contents, otherwise they will return static value.

Sample measure:

Lost SavingsGrand =
VAR Booklist =
    CALCULATETABLE ( VALUES ( TableA[Booking #] ), VALUES ( TableA[Method] ) )
RETURN
    CALCULATE (
        SUM ( TableB[Lost Savings] ),
        FILTER ( ALL ( TableB ), [Booking ID] IN Booklist )
    )

Then use method column and above measure to create table visual.

Regards,

Xiaoxin Sheng

[Leroynz]

Hi Xiaoxim,

Thanks for your reply.

I've tried loading the formula but it is not accepting it. i've included a screenshot of what the error message says.

[Anonymous]

Hi @Leroynz : 

By looking at your screenshot it seems to be a missing some syntax, so I suggest to use the DAX formatter to get the DAX formula formatted properly. I copied the same formula and it seems to be working.

Give it a try and just paste the formatter on in the Power BI and see it it works. 

https://www.daxformatter.com/

[Leroynz]

---

@Anonymous wrote:

Hi @Leroynz : 

By looking at your screenshot it seems to be a missing some syntax, so I suggest to use the DAX formatter to get the DAX formula formatted properly. I copied the same formula and it seems to be working.

Give it a try and just paste the formatter on in the Power BI and see it it works. 

https://www.daxformatter.com/

---

Thanks Jayant,

I've entered the code into the daxformatter site as follows: It works in the site however when I copy it intp Power Query it errors saying the syntax for 'IN' is incorrect. (In Power Query when entering the measure formula the "IN Booklist" has a squiggly red line underneath it

Lost SavingsGrand=
VAR Booklist =
CALCULATETABLE (
VALUES('Raw FCM data'[booking_id]),
VALUES('Raw FCM data'[Method])
)
RETURN
CALCULATE(
SUM('Raw Lost Savings Data'[Lost Savings]),
FILTER(ALL('Raw FCM data'), 'Raw FCM data'[booking_id] IN Booklist)

)
))

[Anonymous · Accepted solution]

Try this 

Lost SavingsGrand=
VAR Booklist =
CALCULATETABLE (VALUES('Raw FCM data'[booking_id]),VALUES('Raw FCM data'[Method]))
RETURN
CALCULATE( SUM('Raw Lost Savings Data'[Lost Savings]), FILTER(ALL('Raw FCM data'), 'Raw FCM data'[booking_id] IN Booklist))

your formula seems to have extrac ending brackets.