Forum Discussion
Remove Row Context in a Calculated Column
Hello. I need help with context in a calculated column. Unfortunately this needs to be a column, can't be a measure.
I have the following data:
| Cod Client | Segment | Product | Volume Sold |
| 6001 | Supermarket | Pasta | 15 |
| 6001 | Supermarket | Cheese | 3 |
| 6001 | Supermarket | Bread | 2 |
| 6002 | Distributor | Pasta | 10 |
| 6002 | Distributor | Cheese | 1 |
| 6002 | Distributor | Bread | 0 |
| 6003 | Supermarket | Pasta | 13 |
6003 | Supermarket | Cheese | 2 |
6003 | Supermarket | Bread | 1 |
My challenge is to add a column with the value of pasta sold for the same cliente reapeted for all the lines of that client.
After that I'll need to get the SUM of all volume sold for clients in the same segment.
Last I'll need a column with the SUM of volume sold of pasta for the same segment.
The result expected:
| Cod Client | Segment | Product | Volume Sold | Volume Pasta Sold | Volume Sold Segment | Volume Pasta Sold Segment |
| 6001 | Supermarket | Pasta | 15 | 15 | 28 | 28 |
| 6001 | Supermarket | Cheese | 3 | 15 | 5 | 28 |
| 6001 | Supermarket | Bread | 2 | 15 | 3 | 28 |
| 6002 | Distributor | Pasta | 10 | 10 | 10 | 10 |
| 6002 | Distributor | Cheese | 1 | 10 | 1 | 10 |
| 6002 | Distributor | Bread | 0 | 10 | 0 | 10 |
| 6003 | Supermarket | Pasta | 13 | 13 | 28 | 28 |
6003 | Supermarket | Cheese | 2 | 13 | 5 | 28 |
6003 | Supermarket | Bread | 1 | 13 | 3 | 28 |
I tried to use:
Volume Pasta Sold = CALCULATE(SUM(Volume Sold), FILTER(ALL(Product), Product = "Pasta"))
But it return blank spaces for products that are not pasta.
Thank you in advance for the help!
Hi Anonymous ,
Try these Calculated Columns
Volume of Pasta Sold = CALCULATE(SUM('Table'[Volume Sold]), FILTER( ALLEXCEPT('Table','Table'[Cod Client]), 'Table'[Product] = "Pasta"))Volume of Pasta Sold Segment = CALCULATE(SUM('Table'[Volume Sold]),FILTER(ALLEXCEPT('Table','Table'[Segment]), 'Table'[Product] = "Pasta"))Cannot understand the logic of the middle column calculation.
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)Did I answer your question? Mark my post as a solution!
Anonymous ,
This one worked for the formula you have given
Volume of pasta Sold 1 = CALCULATE(SUM(Sheet1[Volume Sold]),ALLEXCEPT(Sheet1,Sheet1[Cod Client]),Sheet1[Product]="Pasta")Volume of pasta Sold Segment 1 = CALCULATE(SUM(Sheet1[Volume Sold]),ALLEXCEPT(Sheet1,Sheet1[Segment]),Sheet1[Product]="Pasta")Volume Sold Segment1 = CALCULATE(SUM(Sheet1[Volume Sold]),ALLEXCEPT(Sheet1,Sheet1[Segment],Sheet1[Product]))Please find attached file after signature
4 Replies
Anonymous , did not get is completely you might have to use something like this
Volume Pasta Sold = CALCULATE(SUM(Volume Sold), ALLEXCEPT(Product))
refer
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
Anonymous ,
This one worked for the formula you have given
Volume of pasta Sold 1 = CALCULATE(SUM(Sheet1[Volume Sold]),ALLEXCEPT(Sheet1,Sheet1[Cod Client]),Sheet1[Product]="Pasta")Volume of pasta Sold Segment 1 = CALCULATE(SUM(Sheet1[Volume Sold]),ALLEXCEPT(Sheet1,Sheet1[Segment]),Sheet1[Product]="Pasta")Volume Sold Segment1 = CALCULATE(SUM(Sheet1[Volume Sold]),ALLEXCEPT(Sheet1,Sheet1[Segment],Sheet1[Product]))Please find attached file after signature
Hi Anonymous ,
Try these Calculated Columns
Volume of Pasta Sold = CALCULATE(SUM('Table'[Volume Sold]), FILTER( ALLEXCEPT('Table','Table'[Cod Client]), 'Table'[Product] = "Pasta"))Volume of Pasta Sold Segment = CALCULATE(SUM('Table'[Volume Sold]),FILTER(ALLEXCEPT('Table','Table'[Segment]), 'Table'[Product] = "Pasta"))Cannot understand the logic of the middle column calculation.
Regards,
Harsh Nathani
Appreciate with a Kudos!! (Click the Thumbs Up Button)Did I answer your question? Mark my post as a solution!