Create a new column filtering values from column A and retrieve the correspondent value from columnB

[MigWare]

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

[Icey]

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.

[MigWare]

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

[edhans]

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

[MigWare]

thanks, 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 

[edhans]

Use the IN operator.

Objective = 
IF(
    'Table'[Product] in {"GAS","FOOD"},
    0,
    'Table'[Value]
)

[edhans]

If 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]
)