Row Percentage total and Column percentage total

[fereshtehaghaei]

I need to do an Attrition Rate, in Excel there is a formula for attrition rate for the end of each row here is excel formula =1-(B4+V4)/B4 and when I am trying to translate that into Power BI it would be =1-(Previous Active Count + Sum of the Attrition Row) / Previous Active Count. Also there is an attrtion rate formula for the end of each column, in Excel for instance this is how it is: =1-(B10+K10)/B10 and if I translate that into Power BI it is:  1-(Total Previous Active Count at the end of the column+ Sum of the Attrition Column) / Previous Active Count at the end of the column.
The Attrition Rate is only for the Term and Matriculated Start Term the values are not suppose to show in each row.
What I have gotten so far is below formulas, however I only can show these if I use two matrix tables. It seems that I can't show both measures in one matrix table that one shows the attrition rate at the end of each row and the other shows attrition rate at the end of each column. I came up with two different formulas one to calculate the end of each row for Attrition Rate and one to calculate end of each column.   I tried following the same formula as excel but I had no luck so I am came up with these 2 formulas. Below are my two new formulas, One is for Attrition Rate for Rows and one is for Attrition Rate for Columns:

As of right now this below formula gives the right numbers but they all have a negative signs

Attrition Rate_Term:=

IF(
ISBLANK([Final_Previous Active Count]),
BLANK(),
FORMAT(
DIVIDE(
[Attrition_Final],
[Final_Previous Active Count],
BLANK()
),
"0.00%"
)
)

Here is the Attrition Rate for Rows, this one is giving me the right results for at the end of each row:

Attrition Rate_Cohort:=

VAR _Matriculated = HASONEFILTER(Contact[Matriculated Start Term])

VAR _CurrentActiveCount =
IF(
_Matriculated = FALSE,
SUMX(
CROSSJOIN(
ALLSELECTED(Contact[Matriculated Start Term]),
ALLSELECTED('Term Ordinal'[TermOrdinal])
),
[Current Active Count]
),
[Current Active Count]
)

VAR _AttritionValue =
IF(
_Matriculated = FALSE,
SUMX(
CROSSJOIN(
ALLSELECTED(Contact[Matriculated Start Term]),
ALLSELECTED('Term Ordinal'[TermOrdinal])
),
[Attrition_Final]
),
[Attrition_Final]
)

VAR _Result =
IF(
_CurrentActiveCount > 0,
1 - (_CurrentActiveCount + _AttritionValue) / _CurrentActiveCount,
BLANK()
)

RETURN
FORMAT(_Result, "0.00%")

 

[_AAndrade · Accepted solution]

Hi @fereshtehaghaei,

Please try this DAX formula:

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

[fereshtehaghaei]

This solution worked out great and worked flawlessly! Thank you so much @_AAndrade 

[v-bofeng-msft]

Hi @fereshtehaghaei ，

Could you kindly provide your sample data and Power BI file, and explain how all the numbers in the Excel table shown in the screenshot were calculated?

Best Regards,
Bof