Forum Discussion
Need Help in calculated column
Hello All,
I want to calculate the BPS(Basis Points) by creating new calculated column in Power BI Desktop using DAX.
Formula = Current Year sales - last Year sales *10000
Sample data is below. Can some one assist?
| Country | Product ID | Time Group | Company | Price Tier | Period | Sales Value |
| IND | 1 | MAT | A | Economy | Current Year | 589 |
| CHN | 2 | MAT | B | Premium | Last Year | 548 |
| CAN | 3 | MAT | C | Super Premium | Last two Year | 256 |
| NA | 4 | YTD | k | Economy | Current Year | 564 |
| SA | 5 | YTD | J | Premium | Last Year | 256 |
| SRI LANKA | 6 | YTD | H | Super Premium | Last two Year | 845 |
Hi heetu24 ,
You might consider using the following dax
Test = var _current= SUMX( FILTER(ALL('Table'),'Table'[Period]="Current Year"),[Sales Value]) var _last= SUMX( FILTER(ALL('Table'),'Table'[Period]="Last Year"),[Sales Value]) var _value= _current - _last * 10000 return IF( 'Table'[Product ID]=MINX(ALL('Table'),[Product ID]),_value,BLANK())Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi heetu24 ,
Here are the steps you can follow：
1. Create calculated column.
Test = var _current= SUMX( FILTER(ALL('Table'),'Table'[Period]="Current Year"),[Sales Value]) var _last= SUMX( FILTER(ALL('Table'),'Table'[Period]="Last Year"),[Sales Value]) return _current - _last * 100002. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
It is coming same value in each record I am looking separate value
Hi heetu24 ,
You might consider using the following dax
Test = var _current= SUMX( FILTER(ALL('Table'),'Table'[Period]="Current Year"),[Sales Value]) var _last= SUMX( FILTER(ALL('Table'),'Table'[Period]="Last Year"),[Sales Value]) var _value= _current - _last * 10000 return IF( 'Table'[Product ID]=MINX(ALL('Table'),[Product ID]),_value,BLANK())Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
