Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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!
@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!
@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/
@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.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |