Forum Discussion

fereshtehaghaei's avatar
fereshtehaghaei
Helper II
1 year ago
Solved

Matrix Table Total Row and Total Column

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


 

 

Need Help
Tips and Tricks
  • _AAndrade's avatar
    _AAndrade
    1 year ago

    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.

4 Replies

  • _AAndrade's avatar
    _AAndrade
    Resident Rockstar
    1 year ago

    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's avatar
      fereshtehaghaei
      Helper II
      1 year ago

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

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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-Forum/ba-p/963216

    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/1447523

  • suparnababu8's avatar
    suparnababu8
    Super User
    1 year ago

     

    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.