The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
80 | |
63 | |
56 |
User | Count |
---|---|
248 | |
122 | |
110 | |
77 | |
70 |