Ignore Row Iteraction in Calculation

[PedroRamosBRF]

Hello Community. Hope everything is ok.
Im having a big difficulty here. (i will attach the excel file)
So, the Yellow cell is the result of the written formula

The orange is the result of the formula =(N8-L8)*$K$5.

So far, so good, correct?

So, if i filter the table on the "Key" column, the yellow calculation will change and the orange result will also change, but the formula will be the same (instead of 7.84 on the yellow side, i will have another value - and that is correct)

Now, i need to have the same Powerbi. I have everything in Place, connections done, etc...

My issue is that Pbi uses row iteraction. In other words, i cant find a way to get those 525 again, because the formula =(N8-L8)*$K$5. will not fixate K5, yet it will use the value from K8...

See my point? Hope its clear enought

Im struggling with this :s

Can you help, please?

Thank you all

[PedroRamosBRF]

I will definitely try this solution and let you know. Thank you Bhanu

[bhanu_gautam]

@PedroRamosBRF , In Power BI, you can create measures to hold fixed values that do not change with row context. For example, if $K$5 is a fixed value, you can create a measure for it.

FixedValue = 7.84 // Replace 7.84 with the actual value in $K$5

Replicate the Formula:

CalculatedValue = SUMX(
YourTable,
(YourTable[N] - YourTable[L]) * [FixedValue]
)

Power BI measures automatically respect the filters applied to the data. When you filter the "Key" column, the measure will recalculate based on the filtered data.

[PedroRamosBRF]

Dear @bhanu_gautam thank you for the answer.

Thats the big issue. $K$5 doesnt have a fixed value.  its a weighted average measure, based on all rows values (for our example, 5 rows).
If i filter, the sumproduct will calculate based on the values of the remaining rows.

Then i can divide what i want for the cell $K$5. 

In Pbi i need a way to this also 🙂

[bhanu_gautam · Accepted solution]

@PedroRamosBRF , Create this measure 
WeightedAverage =
VAR TotalWeight = SUM(YourTable[WeightColumn]) -- Replace WeightColumn with the actual column name used for weights
RETURN
SUMX(YourTable, YourTable[ValueColumn] * YourTable[WeightColumn]) / TotalWeight -- Replace ValueColumn with the actual column name for values

CalculatedValue =
SUMX(
YourTable,
(YourTable[N] - YourTable[L]) * [WeightedAverage]
)