Matrix Table Total Row and Total Column

[fereshtehaghaei]

I have the following formula that is currently looking at values in current count and subtract preveious counts and if there are any previous grads then it needs to be counted as positive so it's being added. 
However, this formula, the total under each column is showing a different total which is total column value in current term in active count - total column value in previous term. I want the total to be as what the attrition value is showing so first column -2, second column -5, I hope that makes sense.

I appreciate anyone who can help with this and guide me to the right direction please.

Attrition = 

IF (

    ISBLANK([ActiveLastPer]),

    BLANK(),

    [Active Count] - [ActiveLastPer] + [GRADLastPer]
]

)
The way I created previous grad count is as below: 

GRADLastPer =

VAR _OrderBehind =

IF(

    NOT ISBLANK([GRAD Count]),

    CALCULATE(

        MIN('Term OrdinalN'[Term Ordinal]),

        FILTER(

            ALL('Term OrdinalN'),

            'Term OrdinalN'[Term Label] = SELECTEDVALUE('Term OrdinalN'[Term Label])

            )  

        ) - 1

)

VAR _TermToSearch =

CALCULATE(

    SELECTEDVALUE('Term OrdinalN'[Term Label]),

    FILTER(

            ALL('Term OrdinalN'),

            'Term OrdinalN'[Term Ordinal] = _OrderBehind

            )  

)

VAR _Result =

CALCULATE(

    [GRAD Count],

    FILTER(

            ALL('Term OrdinalN'),

            'Term OrdinalN'[Term Ordinal] = _OrderBehind

            )  

)

RETURN

    _Result

 

 

[_AAndrade · Accepted solution]

Hi @fereshtehaghaei,

Please try this DAX measure:

Attrition_Final = 

VAR _Matriculated = HASONEFILTER(ContactN[Matriculated Start Term])
VAR _TermLabel = HASONEFILTER('Term OrdinalN'[Term Label])

VAR _Result =  IF( 
                    _Matriculated = FALSE() || _TermLabel = FALSE(), 
                    SUMX(
                        CROSSJOIN(
                            ALLSELECTED(ContactN[Matriculated Start Term]),
                            ALLSELECTED('Term OrdinalN'[Term Ordinal])
                        ),
                        [Attriction]
                    ), 
                    [Attriction]
                )
RETURN
    _Result

I hope this could help you.

[fereshtehaghaei]

 @_AAndrade this solution worked the best with my scenario. Thank you so much for taking the time and solving this.

[suparnababu8]

Hi @fereshtehaghaei  
Adjust you Attrition measure with below and try once.

Attrition = 
IF (
    ISINSCOPE('TermOrdinaIN'[Term Label]),  
    IF (
        ISBLANK([ActiveLastPer]),
        BLANK(),
        [Active Count] - [ActiveLastPer] + [GRADLastPer]
    ),
    SUMX(
        SUMMARIZE(
            'TermOrdinaIN',
            'TermOrdinaIN'[Term Label], 
            "AttritionValue", [Active Count] - [ActiveLastPer] + [GRADLastPer]
        ),
        [AttritionValue]
    )
)

 Let me know if it works.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...