Forum Discussion
Solved
dax correction
4_Cur_yr_cost_amt_pur = var SelectedYear = max('yarn data'[Stock_finyear]) var _month_max = CALCULATE(MAX('yarn data'[month_sorter]), FILTER(all('yarn data'), 'yarn data'[Stock_finyear] = Selec...
Hi JothiG
Replace your final multiplication with a SUMX that multiplies per row, inside the same filters:
4_Cur_yr_cost_amt_pur = VAR SelectedYear = MAX('yarn data'[Stock_finyear]) VAR _month_max = CALCULATE( MAX('yarn data'[month_sorter]), ALL('yarn data'), 'yarn data'[Stock_finyear] = SelectedYear ) VAR _month = SELECTEDVALUE('yarn data'[month_sorter]) VAR _month_calc = IF( ISBLANK(_month), _month_max, _month ) RETURN CALCULATE( SUMX( 'yarn data', 'yarn data'[Qty] * 'yarn data'[rate] ), 'yarn data'[Stock_finyear] = SelectedYear, 'yarn data'[month_sorter] = _month_calc )
Hi JothiG
The issue happens because you are multiplying two separate aggregates (SUM(Qty) × SUM(rate)), which gives inflated results. The correct way is to calculate row-by-row (Qty * rate) and then sum them up using SUMX.
4_Cur_yr_cost_amt_pur =
VAR SelectedYear =
MAX ( 'yarn data'[Stock_finyear] )
VAR _month_max =
CALCULATE (
MAX ( 'yarn data'[month_sorter] ),
FILTER ( ALL ( 'yarn data' ), 'yarn data'[Stock_finyear] = SelectedYear )
)
VAR _month =
SELECTEDVALUE ( 'yarn data'[month_sorter] )
VAR _month_calc =
IF ( ISBLANK ( _month ), _month_max, _month )
RETURN
CALCULATE (
SUMX ( 'yarn data', 'yarn data'[Qty] * 'yarn data'[rate] ),
'yarn data'[Stock_finyear] = SelectedYear,
'yarn data'[month_sorter] = _month_calc
)This ensures each row’s (Qty × rate) is computed first, then summed -giving you the correct purchase cost.
this is also not giving correct result.
