Remove Row Context in a Calculated Column

[Anonymous]

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!

[harshnathani · Accepted solution]

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!

[harshnathani · Accepted solution]

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!

[amitchandak]

@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/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

[Anonymous]

@amitchandek, almost. The problem is, I don't want to ignore the product. I want to repeat the volume of past sold for that client, in all the lines for that same client.

[amitchandak · Accepted 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