cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 ClientSegmentProductVolume Sold
6001SupermarketPasta15
6001SupermarketCheese3
6001SupermarketBread2
6002DistributorPasta10
6002DistributorCheese1
6002DistributorBread0
6003SupermarketPasta13

6003

SupermarketCheese2

6003

SupermarketBread1

 

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 ClientSegmentProductVolume SoldVolume Pasta SoldVolume Sold SegmentVolume Pasta Sold Segment
6001SupermarketPasta15152828
6001SupermarketCheese315528
6001SupermarketBread215328
6002DistributorPasta10101010
6002DistributorCheese110110
6002DistributorBread010010
6003SupermarketPasta13132828

6003

SupermarketCheese213528

6003

SupermarketBread113328

 

 

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
Community Champion

Re: Remove Row Context in a Calculated Column

Hi @victorarnaud ,

 

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.

 

1.jpg

 

 

Regards,

Harsh Nathani


Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Remove Row Context in a Calculated Column

@victorarnaud ,

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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
Community Champion

Re: Remove Row Context in a Calculated Column

Hi @victorarnaud ,

 

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.

 

1.jpg

 

 

Regards,

Harsh Nathani


Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Remove Row Context in a Calculated Column

@victorarnaud ,

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

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

Community Blog

Community Blog

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

Upcoming Events

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!

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