Forum Discussion
Subtracting different columns with one specific column
Hello!
I am currently working on a matrix with dates and sum of counts of products.
Row: Product
Column: Date
Values: Sum of Count of Products Sold
| April | May | June | July | August | September | October | |
| Product A | 45 | 333 | 222 | 313 | 43 | 21 | 5 |
| Product B | 55 | 1 | 1 | 0 | 0 | 5 | 6 |
| Product C | 9 | 0 | 0 | 1 | 1 | 0 | 0 |
| Product D | 41 | 76 | 53 | 56 | 77 | 122 | 233 |
I would like my new matrix to have the delta value of Sum of Products Sold for the month and Sum of Products Sold in April
| May | June | July | August | September | October | |
| Product A | 288 | 177 | 268 | -2 | -24 | -40 |
| Product B | -54 | -54 | -55 | -55 | -50 | -49 |
| Product C | -9 | -9 | -8 | -8 | -9 | -9 |
| Product D | 35 | 12 | 15 | 36 | 81 | 192 |
I have created the following measure to achieve this:
Sum of Products = SUM(Product[Product Count])
Sum of Products April = CALCULATE(Sum Of Products, Date_Full_Table[Month_Year] = "April 2020")
Delta = Sum of Products - Sum of Products April
However I get the following result instead
| April | May | June | July | August | September | October | |
| Product A | 0 | 333 | 222 | 313 | 43 | 21 | 5 |
| Product B | 0 | 1 | 1 | 0 | 0 | 5 | 6 |
| Product C | 0 | 0 | 0 | 1 | 1 | 0 | 0 |
| Product D | 0 | 76 | 53 | 56 | 77 | 122 | 233 |
Let me know what should be changed to get me the result I want
I highly appreciate your recommendations and help!
@_ssssaaarra, The formula seems correct, you can try this change
Sum of Products April - CALCULATE(Sum of Products,filter(all(Date_Full_Table), Date_Full_Table[Month_Year] - "April 2020"))
[Delta - Sum of Products]- [Sum of Products April]
But I doubt it's because you use only month, mutiple year getiing data added
2 Replies
- amitchandak
Super User
@_ssssaaarra, The formula seems correct, you can try this change
Sum of Products April - CALCULATE(Sum of Products,filter(all(Date_Full_Table), Date_Full_Table[Month_Year] - "April 2020"))
[Delta - Sum of Products]- [Sum of Products April]
But I doubt it's because you use only month, mutiple year getiing data added