If statement total error

[Anonymous]

Hi Team,

I am writing a simple measure with if condition

if avg fin < avg current fin then sum of Inv_Inv else running total of Inv_Nation, with the below measure my output are correct but my total is not correct.

Measure : 

Inventory_Result_Calculation_Year =

VAR AvgFISCPER = AVERAGE('src_horizon v_horizon_aggregate'[FISCPER])

VAR AvgCurrentFISCPER = AVERAGE('src_horizon v_horizon_aggregate'[CURRENT_FISCPER])

VAR MaxYear = MAX('src_horizon v_horizon_aggregate'[YEAR])

VAR MaxMonthNumber = MAX('src_horizon v_horizon_aggregate'[Month_Number])

VAR MaxWeek = MAX('src_horizon v_horizon_aggregate'[WEEK])

VAR MaxProductGroup = MAX('src_horizon v_horizon_aggregate'[PRODUCT_GROUP])

RETURN

    IF(

       AvgFISCPER < AvgCurrentFISCPER,

        SUM('src_horizon v_horizon_aggregate'[ACT_INV]),

        CALCULATE(

            SUM('src_horizon v_horizon_aggregate'[INVENTORY_NATIONAL]),

            FILTER(ALLSELECTED('src_horizon v_horizon_aggregate'),

                'src_horizon v_horizon_aggregate'[YEAR] <= MaxYear ),

                ALL('src_horizon v_horizon_aggregate') ) )

My expeteced total should be 

  82,20,50,276.81

PowerBi OutPut 

Thanks in advance !

[amitchandak · Accepted solution]

@Anonymous , Try like

Inventory_Result_Calculation_Year =
VAR AvgFISCPER = AVERAGE('src_horizon v_horizon_aggregate'[FISCPER])
VAR AvgCurrentFISCPER = AVERAGE('src_horizon v_horizon_aggregate'[CURRENT_FISCPER])
VAR MaxYear = MAX('src_horizon v_horizon_aggregate'[YEAR])
VAR MaxMonthNumber = MAX('src_horizon v_horizon_aggregate'[Month_Number])
VAR MaxWeek = MAX('src_horizon v_horizon_aggregate'[WEEK])
VAR MaxProductGroup = MAX('src_horizon v_horizon_aggregate'[PRODUCT_GROUP])
VAR Result =
IF(
AvgFISCPER < AvgCurrentFISCPER,
SUM('src_horizon v_horizon_aggregate'[ACT_INV]),
BLANK()
)
RETURN
CALCULATE(
SUM('src_horizon v_horizon_aggregate'[INVENTORY_NATIONAL]),
FILTER(
ALLSELECTED('src_horizon v_horizon_aggregate'),
'src_horizon v_horizon_aggregate'[YEAR] <= MaxYear
),
ALL('src_horizon v_horizon_aggregate'),
Result
)

[Anonymous]

Hi!

Did you fix the problem?

If not, you should consider providing some sample data so that it is easier to debug what may cause problems.

Kind regards