Need Help in calculated column

[heetu24]

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

[Anonymous · Accepted solution]

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.

[heetu24]

It is coming same value in each record I am looking separate value 

[Anonymous]

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 * 10000

2. 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