Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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:
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)
HI @MariaFlorenciaB,
You could achieve that by DAX:
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
Thanks @tringuyenminh92 for your answer.
Is there any posibilitiy to do this calculation in a Calculated Column and not in a Measure?
Thanks!!
Hi @MariaFlorenciaB,
I think yes, we coud use calculated column but there would be a minor bug when users select multiple options in slicer:
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)
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |