Forum Discussion
How to sum quantity sold using data from two columns
Hi Everyone,
I need help on solving my current issue.
I have a sales table that has 3 columns : Item No, Gen No and Qty.
I have a slicer for Item No. If i select a No, i would like to check the "No" selected on the "No" Column and also check if the number is present in the "Gen No" Column and return a sum of quantity where this is fulfilled.
Below is an illustration of my table and the exprected result
| No | Gen No | Qty |
| 1 | 0 | 4 |
| 2 | 0 | 6 |
| 3 | 2 | 8 |
| 4 | 1 | 3 |
| 5 | 3 | 5 |
Expected Result
| No | Qty |
| 1 | 7 |
| 2 | 14 |
| 3 | 13 |
| 4 | 3 |
| 5 | 5 |
Thanks
Anonymous try this measure, In expression, change table and column name as per your data model.
Sum Qty = VAR __no = MAX ( 'Table (2)'[No] ) VAR __qty = SUM ( 'Table (2)'[Qty] ) VAR __genNoQty = CALCULATE( SUM ( 'Table (2)'[Qty] ), ALL ( 'Table (2)'[No] ), 'Table (2)'[Gen No] = __no ) RETURN __qty + __genNoQty
8 Replies
Anonymous not sure it is clear how you are getting the query in output table. can you clarify
Anonymous I see, in original post, you mentioned just sum where gen no value match with no but you are adding the value of no row + value of gen no which matches with no, correcT?