Forum Discussion

JothiG's avatar
JothiG
Icon for Helper III rankHelper III
11 months ago
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] = 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.
Need Help
  • Poojara_D12's avatar
    Poojara_D12
    11 months ago

    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
)

     

     

5 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    11 months ago

    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/1447523

     

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    11 months ago

    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.

  • Poojara_D12's avatar
    Poojara_D12
    Icon for Super User rankSuper User
    11 months ago

    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
)