Forum Discussion
New Column creation based on filtered Measure
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!
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! 🤠
4 Replies
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! 🤠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!
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.
yes.
you calculate for range2
then you return
switch(
true() ,tbl_name[range] = 1 , res1 ,
tbl_name[range] = 2 , res2 ,
....
)
