Power BI Custom column and measure incorrect values but correct subtotal

[Suchitkotak]

I have a power BI dashboard for employee attrition, where I have created a measure that calculates attrition percentage and a custom column that is Date difference of leaving and Date of Joining (Tenure). This measure show correct output when I use it with my date column, but when I use my Tenure column it is not giving me correct output. The subtotal it shows is correct but when I manually add up the rows it does not sum up to the subtotal.

[pratyashasamal]

Hi @Suchitkotak ,
This sounds a use with relationship inbetween the tables . I think there is a relationship between one date column and calender table and the tenture does not have any relationship .
In this senario you need to create a inactive relationship with tenture date column .
Please refer to this link :-
https://learn.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

Thanks ,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[Suchitkotak]

Thank you for your response @pratyashasamal , actually Tenure is a custom created column which consists of bucket values like "1-3 years", "3-5 years" etc depending on how many years employee is in the firm. DAX code is like this:

VAR DateDiffYears = DATEDIFF('Emp'[DOJ],'Emp'[DOL], YEAR)

RETURN

    SWITCH(

        TRUE(),

        ISBLANK(DateDiffYears), "NA",

        DateDiffYears = 0, "0 to 1 year",

        AND(DateDiffYears>= 1,DateDiffYears<3), "1 to 3 years",

        AND(DateDiffYears >= 3, DateDiffYears < 5), "3 to 5 years",

        AND(DateDiffYears >= 5, DateDiffYears< 10), "5 to 10 years",

        DateDiffMonths >10, "More than 10 years"

    )