Forum Discussion

samue_an's avatar
samue_an
Icon for Helper I rankHelper I
5 years ago

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

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)
Need Help

3 Replies

  • samue_an's avatar
    samue_an
    Icon for Helper I rankHelper I
    5 years ago

    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 ???

     

  • HashamNiaz's avatar
    HashamNiaz
    Icon for Solution Sage rankSolution Sage
    5 years ago

    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

     

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    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 )