Calculated column that changes according to a filter in the Report section

[MariaFlorenciaB]

Hi everyone,

I am trying to make a calculated column in Power BI that changes according to the filter in a report, for example:

I have this two tables in my Data section:

Table A

Person	Money
x	$ 123
y	$ 198
z	$ 98

Table B

Currency	Cost
Pounds	$ 16,70
Euro	$ 2

In my report Section I put Currency as a report filter, in order to let the user see the Money in the currency he or she wants.

So I want to create a New Column in Table A, that calculates: Table A[Money] /  Table B[Cost]. I am not being able to do this calculation, because the Data section does not recognize the filter I use in the report.

Is there any solution to my problem?

Thanks in advance.

[tringuyenminh92]

HI @MariaFlorenciaB,

You could achieve that by DAX:

• Create calcuated measure:

Sale Volume = if(HASONEVALUE('Currency'[Cost]),DIVIDE( sum('Transaction'[Money]),FIRSTNONBLANK('Currency'[Cost],'Currency'[Cost])),BLANK() )

i used hasonevalue() to check when multiple currency rates selected, the division value will be blank(). cause if there is not this checking, the result value in Card control will be wrong (this is applied showing in card control)

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

[MariaFlorenciaB]

Thanks @tringuyenminh92 for your answer.

Is there any posibilitiy to do this calculation in a Calculated Column and not in a Measure?

Thanks!!

[tringuyenminh92 · Accepted solution]

Hi @MariaFlorenciaB,

 I think yes, we coud use calculated column but there would be a minor bug when users select multiple options in slicer:

• I crossjoin 2 table Fact & Currency rate table by create calculated table:

Cal table = CROSSJOIN('Transaction','Currency')

Sales Volume = DIVIDE('Cal table'[Money],'Cal table'[Cost])

(in the second picture, there is wrong result that we could not handle or hide)