Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
fereshtehaghaei
Helper II
Helper II

Row Percentage total and Column percentage total

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%")

Snag_ed3d3b0.png

1 ACCEPTED SOLUTION
_AAndrade
Super User
Super User

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




Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




View solution in original post

3 REPLIES 3
_AAndrade
Super User
Super User

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




Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




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

v-bofeng-msft
Community Support
Community Support

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.