DAX subtracting a variable - as basic as that but not working for me ???

[samue_an]

Hi Guys, pulling my hair out with this basic DAX - just returning -1, no clue why returning all variables correctly but when doing the last basic arithmetic it does not like it???? Please can you spot what I'm doing wrong, TIA

WC Total Traffic Vol Increase =

VAR DateRef =

DATE ( "2020", "3", "13" ) // This is my reference date

VAR Territory = "WC" // Reference territory

// Finding the total carried Data Volume for all techs on my reference date

VAR Total_data_vol_ref_day =

CALCULATE (

SUM ( WFH_DATA_TRENDS[Total Data Volume in TB] ),

'WFH_DATA_TRENDS'[Time]= DateRef,

WFH_DATA_TRENDS[Territory] = "WC"

)

// Returning the rows of total data volume/data volume from reference day

VAR result =

CALCULATE (

SUM(WFH_DATA_TRENDS[Total Data Volume in TB]),

WFH_DATA_TRENDS, WFH_DATA_TRENDS[Territory] = "WC"

) --VAR result = DIVIDE(tottraffvolin_tb,Total_data_vol_ref_day)

RETURN

DIVIDE(result-Total_data_vol_ref_day,

Total_data_vol_ref_day)

[Fowmy]

@samue_an 

Check the modified formula below. Also, have a look at the final line, could be 

DIVIDE(result-Total_data_vol_ref_day, result)

WC Total Traffic Vol Increase =
VAR DateRef =
    DATE ( 2020, 3, 13 )
VAR Territory = "WC"
VAR Total_data_vol_ref_day =
    CALCULATE (
        SUM ( WFH_DATA_TRENDS[Total Data Volume in TB] ),
        'WFH_DATA_TRENDS'[Time] = DateRef,
        WFH_DATA_TRENDS[Territory] = Territory
    )
VAR result =
    CALCULATE (
        SUM ( WFH_DATA_TRENDS[Total Data Volume in TB] ),
        WFH_DATA_TRENDS[Territory] = "WC"
    )
RETURN
    DIVIDE ( result - Total_data_vol_ref_day, Total_data_vol_ref_day )

[HashamNiaz]

Hi @samue_an !

Does all the participating variable in Final DIVIDE() expression are of Numeric data type INT/DECIMAL.

Also can you post some sample data. Also try to to wrap your WFH_DATA_TRENDS, WFH_DATA_TRENDS[Territory] = "WC" expression sinside FILTER() liek below;

VAR result =
CALCULATE (
SUM(WFH_DATA_TRENDS[Total Data Volume in TB]), 
FILTER(WFH_DATA_TRENDS, WFH_DATA_TRENDS[Territory] = "WC")
)

See if this solves your issue. Also can you put a Date Slicer only to pick selected date. Seems like you have't connected your Fact table with Calendar / Date Dimension.

Regards,

Hasham

[samue_an]

If I just return all the variables individually they return the correct values, but when I want to subtract the Total_data_vol_ref_day variable highlighted below:

DIVIDE(result-Total_data_vol_ref_day,

Total_data_vol_ref_day)

So basically only 2 calculations finding variables and at the end apply subtraction and division ???