Forum Discussion

Hansolu's avatar
Hansolu
Helper II
9 months ago
Solved

Understanding variable calculation

HI

I am running into an issue understanding the variables behavior. Once i use a var to make my formula more dynamic, the totals are screwed up

BASE:

usage = SUMX ( fact_bom, fact_bom[TotalQty]* [m_fcst_qty_erp] )


Now I want to introduce a base measure, to change the source of the calculation

usage dynamic=
var _base = [m_fcst_qty_erp]
var _res = SUMX ( fact_bom, fact_bom[TotalQty]* _base )
return
_res

the dynamic  version gives me the correct values on row level, but totals are wrong

I understand the vars are only calculated once, but I thought onces per calculation, meaning rows or totals, or where ever a result is calculated.

I do not have a sample file, but its more an understanding problem of some basics.
Hope someone can lighten me up
thanks

Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    9 months ago

    Variables are calculated once per row context in SUMX, not once per entire measure. Your issue is that [m_fcst_qty_erp] is being evaluated outside the row context.

    Fix it by moving the variable inside SUMX:

     

    usage dynamic =
    SUMX(
    fact_bom,
    VAR _base = [m_fcst_qty_erp]
    RETURN
    fact_bom[TotalQty] * _base
    )

6 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    9 months ago

    Hi,

    I tried to create a table to briefly explain how two measures work.

    Please check the below picture, and I hope it helps.

    Because, VAR is calculated once inside the measure, when it comes to the total row context, VAR is calcualted as 27.

     

     

    • Hansolu's avatar
      Hansolu
      Helper II
      9 months ago

      HI

       

      thanks for the explanation, however i am still confused. 

      I thought SUMX is kind of store the row results and at the total level it is summing up all stored results. 

      In my simple example, I can not use var for one element of the calculation inside the sumx as the total would be always wrong?

       

       

       

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    9 months ago

    Variables are calculated once per row context in SUMX, not once per entire measure. Your issue is that [m_fcst_qty_erp] is being evaluated outside the row context.

    Fix it by moving the variable inside SUMX:

     

    usage dynamic =
    SUMX(
    fact_bom,
    VAR _base = [m_fcst_qty_erp]
    RETURN
    fact_bom[TotalQty] * _base
    )

    • v-menakakota's avatar
      v-menakakota
      Community Support
      9 months ago

      Hi Hansolu ,
      Thanks for reaching out to the Microsoft fabric community forum. 

       

      I would also take a moment to thank  Kedar_Pande  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

      I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

       

      Best Regards, 
      Community Support Team  

      • v-menakakota's avatar
        v-menakakota
        Community Support
        9 months ago

        Hi Hansolu ,

        I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

        Best Regards, 
        Community Support Team