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.
Hi,
I have the following two columns in a table, Product and Value, and I need to create a third column that would have the values of Value after being filtered by Column A.
Example: New column would have all the Value of the Products that are not GAS
Product | Value |
GAS | 37,00 |
GAS | 37,00 |
GAS | 36,00 |
GAS | 38,01 |
GAS | 35,00 |
FOOD | 37,00 |
GAS | 37,00 |
GAS | 37,00 |
FOOD | 32,24 |
FOOD | 34,29 |
FOOD | 29,70 |
FOOD | 32,05 |
GAS | 32,62 |
FOOD | 43,57 |
GAS | 38,43 |
GAS | 22,01 |
FOOD | 46,09 |
FOOD | 55,41 |
FOOD | 26,64 |
GAS | 37,57 |
FOOD | 40,16 |
GAS | 40,17 |
FOOD | 31,55 |
WATER | 40,38 |
FOOD | 36,05 |
FOOD | 35,56 |
FOOD | 36,83 |
FOOD | 35,69 |
WATER | 36,28 |
GAS | 38,00 |
GAS | 59,51 |
GAS | 38,12 |
GAS | 37,52 |
GAS | 54,82 |
FOOD | 50,51 |
FOOD | 52,44 |
FOOD | 46,23 |
FOOD | 53,00 |
GAS | 40,53 |
Hi @MigWare ,
I am not very clear what you want. Some possible requirements:
1. Create one column or measure to get the sum values of each product.
Column = CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Product] ) )
Measure = CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Product] ) )
2. You want to show the sum value of each product, expect for the selected product.
a. Create a table.
Product Filter = VALUES('Table'[Product])
b. Create a measure.
Condition = IF ( MAX ( 'Table'[Product] ) IN VALUES ( 'Product Filter'[Product] ), 1, 0 )
c. Put the condition measure on "Filters on this visual" of the visuals you want to filter.
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks but that was not the aim of my question, trying to make it clear.
What I have in the table:
Product | Value |
GAS | 37,00 |
GAS | 37,00 |
GAS | 36,00 |
GAS | 38,01 |
GAS | 35,00 |
FOOD | 37,00 |
GAS | 37,00 |
GAS | 37,00 |
FOOD | 32,24 |
FOOD | 34,29 |
FOOD | 29,70 |
FOOD | 32,05 |
GAS | 32,62 |
FOOD | 43,57 |
GAS | 38,43 |
GAS | 22,01 |
FOOD | 46,09 |
FOOD | 55,41 |
FOOD | 26,64 |
GAS | 37,57 |
FOOD | 40,16 |
GAS | 40,17 |
FOOD | 31,55 |
WATER | 40,38 |
FOOD | 36,05 |
FOOD | 35,56 |
FOOD | 36,83 |
FOOD | 35,69 |
WATER | 36,28 |
GAS | 38,00 |
GAS | 59,51 |
GAS | 38,12 |
GAS | 37,52 |
GAS | 54,82 |
FOOD | 50,51 |
FOOD | 52,44 |
FOOD | 46,23 |
FOOD | 53,00 |
GAS | 40,53 |
What I want to achieve:
Product | Value | Objective |
GAS | 37,00 | 0 |
GAS | 37,00 | 0 |
GAS | 36,00 | 0 |
GAS | 38,01 | 0 |
GAS | 35,00 | 0 |
FOOD | 37,00 | 37,00 |
GAS | 37,00 | 0 |
GAS | 37,00 | 0 |
FOOD | 32,24 | 32,24 |
FOOD | 34,29 | 34,29 |
FOOD | 29,70 | 29,70 |
FOOD | 32,05 | 32,05 |
GAS | 32,62 | 0 |
FOOD | 43,57 | 43,57 |
GAS | 38,43 | 0 |
GAS | 22,01 | 0 |
FOOD | 46,09 | 46,09 |
FOOD | 55,41 | 55,41 |
FOOD | 26,64 | 26,64 |
GAS | 37,57 | 0 |
FOOD | 40,16 | 40,16 |
GAS | 40,17 | 0 |
FOOD | 31,55 | 31,55 |
WATER | 40,38 | 40,38 |
FOOD | 36,05 | 36,05 |
FOOD | 35,56 | 35,56 |
FOOD | 36,83 | 36,83 |
FOOD | 35,69 | 35,69 |
WATER | 36,28 | 36,28 |
GAS | 38,00 | 0 |
GAS | 59,51 | 0 |
GAS | 38,12 | 0 |
GAS | 37,52 | 0 |
GAS | 54,82 | 0 |
FOOD | 50,51 | 50,51 |
FOOD | 52,44 | 52,44 |
FOOD | 46,23 | 46,23 |
FOOD | 53,00 | 53,00 |
GAS | 40,53 | 0 |
use an IF function.
It is better to do this in Power Query, but I'll give both PQ and DAX:
Power Query:
Add a conditional column that will essentially generate this formula:
if [Product] = "GAS" then 0 else [Value]
It will return this table
If you want a calculated column (not advised), use this formula:
Objective =
IF(
'Table'[Product] = "GAS",
0,
'Table'[Value]
)
In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingthanks, that was the objective. One question, if i want to add another product on the DAX expression
Objective = IF( 'Table'[Product] = "GAS", 0, 'Table'[Value] )
, is that possible? I´ve tried to use 'Table'[Product] = {"GAS","FOOD"} but i get an error saying a table of multiple values was supplied where a single value was expected
Use the IN operator.
Objective =
IF(
'Table'[Product] in {"GAS","FOOD"},
0,
'Table'[Value]
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIf your table is called sample, the following calculated column will work.
EDIT: Not sure of your requirement. You might need to change the 7th line below to <> CurrentProduct.
If that isn't right either, be clearer on your expected result please with some sample output.
Total Values =
VAR CurrentProduct = 'Sample'[Product]
RETURN
SUMX(
FILTER(
ALL('Sample'),
'Sample'[Product] = CurrentProduct
),
'Sample'[Value]
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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 |
---|---|
114 | |
100 | |
78 | |
75 | |
50 |
User | Count |
---|---|
144 | |
109 | |
108 | |
87 | |
61 |