dax correction

[JothiG]

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)

var qty =

CALCULATE(

SUM('yarn data'[Qty]),

  'yarn data'[Stock_finyear] = SelectedYear,

   'yarn data'[month_sorter] = _month_calc

)

var rat =

CALCULATE(

 SUM('yarn data'[rate]),

'yarn data'[Stock_finyear] = SelectedYear,

'yarn data'[month_sorter] = _month_calc)

RETURN

qty * rat    --- it is not giving correct result .why? it returns correct qty and rat is also correct. multiplication results only gives wrong answer.

[Poojara_D12 · Accepted solution]

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
)

[rohit1991]

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.

[JothiG]

this is also not giving correct result.

[danextian]

Hi @JothiG 

It seems to me that you are trying to get the product of  aggregations for each row and them sum them up. But that's just my guess. There's no enough information to go on with. Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud. https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Praful_Potphode]

Hi @JothiG 

can you share more information(Scenario\Data Model\PBIX) on this?