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
Highlighted
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
Highlighted
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
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,842)