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 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!
Solved! Go to Solution.
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 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 ,
....
)
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!
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 |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |