cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Champion

## Re: Remove Row Context in a Calculated Column

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)

Highlighted
Super User IV

## Re: Remove Row Context in a Calculated Column

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

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

4 REPLIES 4
Highlighted
Super User IV

## Re: Remove Row Context in a Calculated Column

@victorarnaud , 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/

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

## Re: Remove Row Context in a Calculated Column

@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.

Highlighted
Community Champion

## Re: Remove Row Context in a Calculated Column

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)

Highlighted
Super User IV

## Re: Remove Row Context in a Calculated Column

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

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors