Forum Discussion

PedroRamosBRF's avatar
PedroRamosBRF
New Member
1 year ago
Solved

Ignore Row Iteraction in Calculation

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

Need Help
Tips and Tricks
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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]
    )

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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's avatar
      PedroRamosBRF
      New Member
      1 year ago

      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's avatar
        bhanu_gautam
        Super User
        1 year ago

        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]
        )