Forum Discussion
Need help with DAX Measure
Hi Community,
I need help with a measure that I trying to modify in a model.
Transactions table -
| ID | Type | IsRevenue | IsBundleSKU | ProductID | UnitLineQty | BundleQty |
| 123 | Net Sales | 1 | 0 | 123A | 1 | 0 |
| 456 | Net Sales | 1 | 1 | 123Bundle | 0 | 2 |
| 998 | Net Sales | 1 | 1 | 214B | 0 | 1 |
| 1000 | Net Sales | 1 | 0 | 567Bundle | 1 | 0 |
| 479 | Net Sales | 1 | 0 | 214Bundle | 1 | 0 |
I am trying to create a single measure (not a calculated column) called InvoicedQty that will go through two filters to calculate the Sum on the Unit Line Qty and Bundle Qty.
Here are the two filter conditions which cannot be summed:
Type ='Net Sales' && IsRevenue =1 THEN SUM(UnitLineQty)
Type ='Net Sales' && IsRevenue =1 && IsBundle =1 THEN SUM(BundleQty)
5 Replies
barb_19 ,
try this, if not this is expected please share the screenshot of expected output.
Sum on the Unit Line Qty and Bundle Qty = VAR _sumOfUnitLineQty = CALCULATE( SUM('Table (3)'[UnitLineQty]),'Table (3)'[Type] = "Net Sales",IsRevenue =1) VAR _sumOfBundleeQty = CALCULATE( SUM('Table (3)'[BundleQty]),'Table (3)'[Type] = "Net Sales",IsRevenue =1,IsBundle =1) VAR _output = _sumOfUnitLineQty+_sumOfBundleeQty RETURN _outputThanks,
Thanks for sharing the solution. However, adding the two columns is skewing the numbers due to the discrepancy in the data. Is is possible to not add the two columns?
Hi,
can you post the desired achievement (your expected result)?
Desired Results: A new column that sums respective Qty based on the filter criteria
ID Type IsBundleSKU ProductID UnitLineQty BundleQty InvoicedQty 123 Net Sales 0 123A 1 0 1 456 Net Sales 1 123Bundle 0 2 2 998 Net Sales 1 214B 0 1 1 479 Net Sales 0 214Bundle 1 0 1
There is no ISREVENUE() column in your data. Please share a proper dataset and show the expected result.
