Hi everyone. I would appreciate some help with a calculation. I have a table with both sale invoice line items and credit memo line items. Each row contains a single line from an invoice or credit memo with the item #, quantity, price, total amount, etc. There is a “Document Type” field that contains “INV” or “CM” and a currency code (USD, EUR, etc.) that the price and amount represent.
The quantity and amount fields are always positive, so I need to multiply them by -1 when Document Type = CM. I’ve created the following measure for Quantity (as an example), following a DAX structure I learned in the E-DNA Financial Reporting course.
Total Quantity =
SUMX(‘Sales Invoice Line’,
VAR CurrentLineQuantity = SELECTEDVALUE(‘Sales Invoice Line’[Quantity])
VAR CurrentDocumentType = SELECTEDVALUE(‘Sales Invoice Line’[Document Type])
Return
SWITCH(TRUE(),
CurrentDocumentType=“CM”,( CurrentLineQuantity * -1), //make credit memos negative
CurrentLineQuantity
))
My challenge is when I put the Currency Code and Total Quantity measure into a table visual, I don’t get any results (just blank). I’m trying to avoid adding columns to the underlying table (which of course is one approach), but it seems using measures is the better route. Thanks!
Good evening @murphy00 ,
Welcome to the forum !
A Dax formula which will work could be :
Total sales = SUMX( FactT, if(FactT[DocType] = "CM", -1, 1) * FactT[QTY] * FactT[Amount] )
which turn all invoice lines with Document type “CM” into a negative value.
With —X formulas, like SUMX, fieldnames within the table can be selected in the formulas
The SUMX formula iterates each line of the table, no need for a “selectedvalue” in this case.
PBIX with some dummy data attached:
Sales Totals.pbix (41.4 KB)
This is usually not possible within formulas NOT ending with a X , like “SUM”
NOT working: SUM( if(FactT[DocType] = “CM”, -1, 1) * FactT[QTY] * FactT[Amount] )
Kind regards,
Delta Select
1 Like
Hi @deltaselect! Thanks very much for the quick response. In the meantime, I was doing some additional googling and came across this article. Best practices using SUMMARIZE and ADDCOLUMNS - SQLBI
It does a great job explaining things, but all of the caveats were leading me to think it would be easier to physically add “physical” columns. So, your reply is just in time - with much appreciation.
However, I just tried it, and the result does not seem to handle the row context when adding another dimension such as Currency from the same FactT. Here’s an example result:
Here’s the measure:
Total Quantity = SUMX(‘Sales Invoice Line’,
if(‘Sales Invoice Line’[Document Type]=“CM”, -1, 1)
* ‘Sales Invoice Line’[Quantity]
)
I also tried putting the expression inside a CALCULATE based on some of the advice in the article linked above. However, that gave me this error: A single value for column ‘Document Type’ in table ‘Sales Invoice Line’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Here’s that attempt:
Total Quantity = SUMX(‘Sales Invoice Line’,
CALCULATE(if(‘Sales Invoice Line’[Document Type]=“CM”, -1, 1)
* ‘Sales Invoice Line’[Quantity], ALLSELECTED()
)
)
Note that I’ll need to filter and show intermediate totals of these measures using both fields in the same invoice line table (such as currency code) as well as linked master tables (e.g., customer name, item #, etc.) I’d welcome any other advice, please. 
Hi @murphy00
Your table with repeating the same totals per currency seems have to do with your data model, do you want to consolidate the different currency values ?
The formula presented always works.
Providing a non-data sensitive, to the point, PBIX would help for further advice in this issue.
KR Delta Select
You were 100% right. I had picked the wrong table’s currency code 
. I’ve been able to take your pattern and apply it to several fields. Thanks again very much! I’ve flagged your initial reply as the solution and liked-it.