Forum Discussion
Divide 2 Columns with condition
Hey,
I would like to divide on column by another (but only by specific value). So I have a table with oil/gas prices for all Europe in EUR (column weekly price per l) on specific dates of time. I would like calculate prices for all countries in PLN only.
So let's say Bulgaria has price for Euro-Super 96 for 1,7241 Euro on 4th on July. Conversion rate in PLN for this date is 0,21314. So my outcome would be - Euro-SUper 95 on 4th of July cost 8.120519
I wonder if it's possible with DAX? I could create a conversion rate table and join it by date and then I guess it would work.
Hi Anonymous
Thanks for reaching out to us.
>> So let's say Bulgaria has price for Euro-Super 96 for 1,7241 Euro on 4th on July. Conversion rate in PLN for this date is 0,21314. So my outcome would be - Euro-SUper 95 on 4th of July cost 8.120519
please try this,
Column = var _Country="Bulgaria" var _WeeklyPWPI=MAXX(FILTER('Table','Table'[Country Name]=_Country && 'Table'[Prices in force on]= EARLIER('Table'[Prices in force on]) && 'Table'[Product Name]= EARLIER('Table'[Product Name])),[Weekly Price with per I]) var _divide=DIVIDE(_WeeklyPWPI,'Table'[Euro exchange rate]) return IF('Table'[Country Name]="Poland", _divide)in this column code, _WeeklyPWPI returns 1,7241
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- lbendlin
Super User
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - Ashish_Mathur
Hi,
Share some data and show the expected result.
v-xiaotangCommunity Support
Hi Anonymous
Thanks for reaching out to us.
>> So let's say Bulgaria has price for Euro-Super 96 for 1,7241 Euro on 4th on July. Conversion rate in PLN for this date is 0,21314. So my outcome would be - Euro-SUper 95 on 4th of July cost 8.120519
please try this,
Column = var _Country="Bulgaria" var _WeeklyPWPI=MAXX(FILTER('Table','Table'[Country Name]=_Country && 'Table'[Prices in force on]= EARLIER('Table'[Prices in force on]) && 'Table'[Product Name]= EARLIER('Table'[Product Name])),[Weekly Price with per I]) var _divide=DIVIDE(_WeeklyPWPI,'Table'[Euro exchange rate]) return IF('Table'[Country Name]="Poland", _divide)in this column code, _WeeklyPWPI returns 1,7241
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
