cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IanWaring
Helper III
Helper III

Subtract two columns in view (different but joined data sources)

I have a list of largest customer invoice line value, which I want to match against the sum of all customer aged balances for each account in our Dynamics 365 Finance System (entites are FreeTextInvoiceHeader, FreeTextInvoiceLines, CustAgedBalancesMST):

Account  Customer Name  Invocie #    Invoice Date  Invoice Value  SumOfAmountDueMST    Delta
CJ00001  ACME CORP        FTI-0044    04/04/20        $104,567               $120,012                    -$15,445

CJ00002  BEEPBEEP LLC     FTI-0089     04/05/20         $10,000                      $950                       $9,050

However, I can't get the subtraction of Delta = Invoice Vale - SumOfAmountDueMST working at all. I've spent also a day trying to do it and scouring forums in a vain attempt to subtract the numbers in two adjacent columns to give me that delta value. Any ideas how to do this?

FWIW, this gives us a clue as to whether they've paid that one big invoice a year or not yet.

1 ACCEPTED SOLUTION

@IanWaring  You should be able to do a lookup using the related function, but that will make your problem harder I suspect, as you don't want to aggregate the amt due any further than it already is - you only want it displayed once per order line, and then subtract the aggregate of order lines. 

 

Just to confirm, you're creating a new MEASURE (not column) for this DAX correct? 

 

Can you share a sample file with sample data? I have tried to mock up a similar scenario; see the attached file below my signature. I used this measure: 

 

Measure Outstanding Amt = MAX(Customers[SumAmtDue])-SUM(SalesItems[UnitPrice])
 

 

 


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

21 REPLIES 21
IanWaring
Helper III
Helper III

And my boss now has the finished work on her mobile, so all our account managers and their management can see who's yet to pay their Institution subsscriptions. All done, happy CFO and this will really help our DSO

 

Thank you. 🙂

IanWaring
Helper III
Helper III

Allison - you're a star. Works as a filter (though I can't display it in the table) - but the filter is all I needed. Thank you!

@IanWaring Yay! Sorry I didn't specify earlier that it was a measure. Glad it's working. You can use it as filter, or in values, but not in slicer or axis/columns.



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

IanWaring
Helper III
Helper III

Thank you Allison,

I had been trying to add a Column, so will try the measure in the morning (been out all day on a mercy dash to get Christmas presents to all relatives now afflicted by sudden CoronaVirus lockdowns across the UK). So will try in the morning, and if I still can't get it to work, can furnish you with some sample data or access to same (all the tables are behind an ODATA connection into our Dynamics 365 Finance & SCM 10.0.15 system).

I really do appreciate your help - thank you.

IanWaring
Helper III
Helper III

Each account number only appears once in the CustAgedBalances table - just wonder if I shoud just to a lookup to pick up the SumOfAmountDueMST value when CustAgedBalances([CustAccount]) is the same as the CustomersV3([CustomerAccount]) number i'm pulling the rest of the data from...

IanWaring
Helper III
Helper III

Really appreciate your help Allison, but...

DELTACOL = SUM(FreeTextInvoiceLines[UnitPrice])-(SELECTEDVALUE(CustAgedBalances[SumOfAmountDueMst]))
 
gives:
 
image.png
 

@IanWaring  You should be able to do a lookup using the related function, but that will make your problem harder I suspect, as you don't want to aggregate the amt due any further than it already is - you only want it displayed once per order line, and then subtract the aggregate of order lines. 

 

Just to confirm, you're creating a new MEASURE (not column) for this DAX correct? 

 

Can you share a sample file with sample data? I have tried to mock up a similar scenario; see the attached file below my signature. I used this measure: 

 

Measure Outstanding Amt = MAX(Customers[SumAmtDue])-SUM(SalesItems[UnitPrice])
 

 

 


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

Likewise the other formula - same total 😞

 

AllisonKennedy
Super User III
Super User III

@IanWaring  It will be really difficult if the relationships are many to many. If they're 1:many as in your latest screenshot of them, you should be able to try either:

 

DELTACOL = SUM(FreeTextInvoiceLines[UnitPrice])-MAX(CustAgedBalances[SumOfAmountDueMst])

 

or

 

DELTACOL = SUM(FreeTextInvoiceLines[UnitPrice])-SELECTEDVALUE(CustAgedBalances[SumOfAmountDueMst])



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

IanWaring
Helper III
Helper III

Adding in CustomersV3 entity, setting all the joins around this, same result. Now attempting to use LOOKUPVALUE and RELATED. Getting very depressed with this now. 2 days so far to subtract numbers in adjacent columns...

IanWaring
Helper III
Helper III

Think i'll put the customer table in there, where the account number is unique, and join everything off that

IanWaring
Helper III
Helper III

I've made the links bidrectional now but that's had no effect. Still getting the same total on every single line.

CustAgedBalances will contain the same account number several times for different payment windows (current, 30-60 days, 60-90 days, 90+ days) balances due plus the total of all (which i'm reporting on).

There are probably several FreeTextOrderheaders containing the customer account number. That's the common key to join the aged balances.

There are likely to be several FreeTextOrderLines per FreeTextOrderHeader, but they are joined by a unique order reference.


From what've said, the relationships should all be many:many. I'll try that next...

AllisonKennedy
Super User III
Super User III

@IanWaring  I need to know cardinality and cross filter direction of the relationships too please. Can you share screenshot of model view or provide that info? 

 

If SELECTEDVALUE gives blank, that means there is more than one value for one or both of the columns in the table. 

 

You're using a table visual, right? What is the aggregation for each of the columns in that table?



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

IanWaring_0-1608329214847.png

 

SUM of UnitPrice

SumofAmountDueMST is not summarised

AllisonKennedy
Super User III
Super User III

@IanWaring  what is the relationship between the tables and how does raw data look like? 

 

If you had to set to 'don't summarize' you may find that SELECTEDVALUE works better than SUM, or MAX is a cheat to get the same thing but even when more than one value exists. 

 

Sample data and relationships would be really helpful.



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

DELTACOL = MAX(FreeTextInvoiceLines[UnitPrice])-MAX(CustAgedBalances[SumOfAmountDueMst])

gives

 

IanWaring_0-1608328063782.png

 

IanWaring_1-1608328227903.png

 

btw, using SELECTEDVALUE both sides instead of MAX just blanks the column completely.

AllisonKennedy
Super User III
Super User III

@IanWaring  If your table is working correctly and gives the correct values, then you just need to use the same aggregation/summarization that you used in the table. I'm guessing it is SUM. So try: 

 

Delta = SUM(Table1[Invoice Vale]) - SUM(Table2[SumOfAmountDueMST])



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

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 on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors