New Column creation based on filtered Measure

[shas]

Hi,

I have the sample data as below.

category	range	year	Price	Variance
Category1	1	2020	1000	-200
Category1	2	2020	1200	50
Category2	3	2021	1600	250
Category2	4	2022	1700	-150
Category2	5	2021	1800	100
Category3	1	2022	2000	200
Category3	2	2023	2500	-300
Category3	3	2022	600	-50
Category3	4	2021	8000	250
Category3	5	2020	9000	800

I have created a measure which (price+Variance)/price. using Calculate function filtered this measure to  category=category1 and range=1 which gave me a value 0.8 now I need to use this value  from this measure and multiple to price column and create a new column but when i do that the value is displayed in 1st row which has the filters of measure and all other rows are empty. I need this operation to be applied to every row in the new column.

Thank you!

[shas]

Hi 

This works fine for the scenario i have described but if i want to use the res* price for range1 and calculate another res for range2 and use that as res for all the range2 and so on for other ranges is that possible.

[Daniel29195]

@shas 

yes. 

you calculate for range2 

then you return 

switch(
true() , 

tbl_name[range] = 1 ,   res1 , 

tbl_name[range] = 2 , res2 , 

....

) 

[Daniel29195 · Accepted solution]

@shas 

output : 

if i understood correcrtly, you created a measure  = (price+Variance)/price with calculate to filter on category 1 and range 1 .

then you are using this measure in a calculated column to multiply it with price .

if that so, 

let me help you :

create a calculated column as follow : 

Column = 
var datasource= 
FILTER(
    'Table (2)',
    'Table (2)'[category] = "Category1" &&  'Table (2)'[range] = 1
)
var price =SELECTCOLUMNS(datasource, 'Table (2)'[Price])
var vriance = SELECTCOLUMNS(datasource, 'Table (2)'[Variance])
var res =  (price+ vriance)/price

return res * 'Table (2)'[Price]

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution !✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[shas]

Hi

This solve the problem of showing the value in all rows but the filtering does not work it calculates the res value using all the rows in the table.

Thank you!