Multiplying a column by a filtered value on another column

[Anonymous]

Hey, 

I wonder if someone can help me out with this issue, I'll try to be as compact and clear as possible.

Imagine 2 tables: TbFactor and TbValue similar to what's described below. In my ERD they are connected through the YEAR column. I would like to find a way to, when I click a checkbox in my dashboard, multiply the VALUE column in TbValue by the corresponding factor in TbFactor - e.g: For the 1st row in TbValue, VALUE would be equal to 230 * 1.8. Please refer to the example tables below:

TbFactor

YEAR	FACTOR
2020	1.8
2021	1.5
2022	1.2
2023	1

TbValue

YEAR	Col2	Col3	VALUE
2020	(...)	(...)	230
2020	(...)	(...)	399
2021	(...)	(...)	259

The closest I could arrive to was this:

C_01_idx_2 = IF(ISFILTERED(AUX[Column1]), MAX(TbValue[Value])*MAX(TbFactor[Factor]), MAX(TbValue[Value]))

I can't seem to make match without having to aggregate via MAX(), and this (expectedly) multiplies the column only by the highest factor, without taking year into account

I'm quite new to PowerBI so there might be something basic that I'm missing. Appreciate any tips.

Thanks!

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

You can refer to the following measure

C_01_idx_2 = IF(ISFILTERED(AUX[Column1]),MAXX(FILTER(TbFactor,[YEAR]=SELECTEDVALUE(TbValue[Year])),[Factor])*SELECTEDVALUE(TbValue[VALUE]),SELECTEDVALUE(TbValue[VALUE]))

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This worked very well with my data. Thanks for the help!