cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Leroynz Frequent Visitor
Frequent Visitor

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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

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

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.

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Leroynz Frequent Visitor
Frequent Visitor

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

 

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.

 

Error.PNGFormula.PNG

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

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 Frequent Visitor
Frequent Visitor

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


@jayant_patel 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)

)
))

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

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.

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 398 members 3,411 guests
Please welcome our newest community members: