Understanding variable calculation

[Hansolu]

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

[Kedar_Pande · Accepted solution]

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]

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]

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 

[v-menakakota]

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 

[Jihwan_Kim]

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]

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?