Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
AjayReddy
Regular Visitor

Unable to complete %Formula

‎07-13-2022 11:03 PM

Hi,

 

I am trying to prepare a attrition dashboard, I am unable to calculate the below requirement in power bi

 

Have 2 datasets linked by independent date table

1 HC file

2 Attrition file

 

I am able to calculate the monthly attrition which attrition/hc for that month

For the annualized attrition the formula I need to use is: (Sum of monthly attrition rate * 12)/month number (if its mar it should be 3)

 

Please help with a solution, provided an example table below

 

 JanFebMar
Attrition1089
HC100105100
Monthly Attrition10%8%9%
Annualized Attrition120%106%106%

 

Thanks,

AJ

Labels:
Message 1 of 9
911 Views
0
8 REPLIES 8
amitchandak
Super User
Super User

‎07-13-2022 11:23 PM

@AjayReddy , Try like

 

AverageX(Values('Date'[Month Year]), [Monthly Attrition]*12)

 

or


SumX(Values('Date'[Month Year]), [Monthly Attrition]*12)/12

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Message 2 of 9
881 Views
0
AjayReddy
Regular Visitor
In response to amitchandak

‎07-13-2022 11:43 PM

Hi Amit,

 

Thanks for the response, I am getting the same values as monthly attrition %, what should be here SumX(Values('Date'[Month Year]), month number?

 

Thanks,

AJ

Message 3 of 9
875 Views
0
Anonymous
Not applicable
In response to AjayReddy

‎07-18-2022 01:02 AM

Hi @AjayReddy ,

 

Not sure what the model looks like. 

Please try these:

Table = 
SUMMARIZE (
    'Calendar',
    'Calendar'[Month],
    "attrition",
        CALCULATE (
            SUM ( Attrition[Attrition] ),
            'Calendar'[Month] = MAX ( 'Calendar'[Month] )
        ),
    "hc", CALCULATE ( SUM ( 'HC'[HC] ), 'Calendar'[Month] = MAX ( 'Calendar'[Month] ) ),
    "monthly attrition",
        DIVIDE (
            CALCULATE (
                SUM ( Attrition[Attrition] ),
                'Calendar'[Month] = MAX ( 'Calendar'[Month] )
            ),
            CALCULATE ( SUM ( 'HC'[HC] ), 'Calendar'[Month] = MAX ( 'Calendar'[Month] ) )
        )
)
annualized attrition = 
VAR _month = 'Table'[Month]
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[monthly attrition] ),
        FILTER ( 'Table', 'Table'[Month] <= EARLIER ( 'Table'[Month] ) )
    ) * 12
VAR _result =
    DIVIDE ( _sum, _month )
RETURN
    _result

vcgaomsft_0-1658131237869.png

The PBIX file is attached for reference.

 

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Unable to complete %Formula.pbix
Message 4 of 9
845 Views
0
AjayReddy
Regular Visitor
In response to Anonymous

‎07-19-2022 10:26 PM

Hey,

 

Thank you for the response,

 

I am not able to perform the below step maybe because its a measure

SUM ( Attrition[Attrition] )

 

Just to explain the model a little more, have 2 files mentioned below

1) HeadCount file with all employee names month on month (names can be repeated if the empoyee does not attrite to the next month) stacked on top of each other

2) YTD attrition file

 

Prepared a date table with dates till end of the year. Date table is linked to the HC data on the HC report date and to the attrition table to the separation date

Message 5 of 9
836 Views
0
Anonymous
Not applicable
In response to AjayReddy

‎07-20-2022 12:18 AM

Hi @AjayReddy ,

 

Please try to replace this formula with that measure.

If this does not work, please consider providing a PBIX file that does not contain private data.

How to provide sample data in the Power BI Forum

 

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Message 6 of 9
831 Views
0
AjayReddy
Regular Visitor
In response to Anonymous

‎07-20-2022 08:32 PM

Hey,

 

Did you miss the formula cause I do not see it

"Please try to replace this formula with that measure."

 

Thanks,

AJ

Message 7 of 9
827 Views
0
Anonymous
Not applicable
In response to AjayReddy

‎07-20-2022 08:45 PM

Hi AJ,

 

Sorry I didn't explain clear, I meant to use measure [Attrition] to replace SUM ( Attrition[Attrition] ) in this formula.

Table = 
SUMMARIZE (
    'Calendar',
    'Calendar'[Month],
    "attrition",
        CALCULATE (
            [Attrition],
            'Calendar'[Month] = MAX ( 'Calendar'[Month] )
        ),
    "hc", CALCULATE ( SUM ( 'HC'[HC] ), 'Calendar'[Month] = MAX ( 'Calendar'[Month] ) ),
    "monthly attrition",
        DIVIDE (
            CALCULATE (
                [Attrition],
                'Calendar'[Month] = MAX ( 'Calendar'[Month] )
            ),
            CALCULATE ( SUM ( 'HC'[HC] ), 'Calendar'[Month] = MAX ( 'Calendar'[Month] ) )
        )
)

If this does not work, please consider providing a PBIX file that does not contain private data.

How to provide sample data in the Power BI Forum

 

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Message 8 of 9
824 Views
0
AjayReddy
Regular Visitor
In response to Anonymous

‎07-21-2022 11:00 PM

Thank you for the quick response, Below is the error I am getting

AjayReddy_0-1658468372188.png

I am attaching a sample file with fake data for your understanding

HC

Report DateEmployee Name
31-Jan-22AJ
31-Jan-22VJ
31-Jan-22RJ
31-Jan-22abcd
31-Jan-22AJ
31-Jan-22VJ
31-Jan-22RJ
31-Jan-22abcd
31-Jan-22AJ
31-Jan-22VJ
31-Jan-22RJ
31-Jan-22abcd
31-Jan-22AJ
31-Jan-22VJ
31-Jan-22RJ
31-Jan-22abcd
31-Jan-22AJ
31-Jan-22VJ
31-Jan-22RJ
31-Jan-22abcd
31-Jan-22AJ
31-Jan-22VJ
31-Jan-22RJ
31-Jan-22abcd
31-Jan-22AJ
31-Jan-22VJ
31-Jan-22RJ
31-Jan-22abcd
31-Jan-22AJ
28-Feb-22VJ
28-Feb-22RJ
28-Feb-22abcd
28-Feb-22AJ
28-Feb-22VJ
28-Feb-22RJ
28-Feb-22abcd
28-Feb-22AJ
28-Feb-22VJ
28-Feb-22RJ
28-Feb-22abcd
28-Feb-22AJ
28-Feb-22VJ
28-Feb-22RJ
28-Feb-22abcd
28-Feb-22AJ
28-Feb-22VJ
28-Feb-22RJ
28-Feb-22abcd
28-Feb-22AJ
28-Feb-22VJ
28-Feb-22RJ
28-Feb-22abcd
28-Feb-22AJ
28-Feb-22VJ
28-Feb-22RJ
28-Feb-22abcd
28-Feb-22AJ
28-Feb-22VJ
28-Feb-22RJ
28-Feb-22abcd
28-Feb-22AJ
28-Feb-22VJ
28-Feb-22RJ
31-Mar-22abcd
31-Mar-22AJ
31-Mar-22VJ
31-Mar-22RJ
31-Mar-22abcd
31-Mar-22AJ
31-Mar-22VJ
31-Mar-22RJ
31-Mar-22abcd
31-Mar-22AJ
31-Mar-22VJ
31-Mar-22RJ
31-Mar-22abcd
31-Mar-22AJ
31-Mar-22VJ
31-Mar-22RJ
31-Mar-22abcd
31-Mar-22AJ
31-Mar-22VJ
31-Mar-22RJ
31-Mar-22abcd
31-Mar-22AJ
31-Mar-22VJ
31-Mar-22RJ
31-Mar-22abcd
31-Mar-22AJ
31-Mar-22VJ
31-Mar-22RJ
31-Mar-22abcd
31-Mar-22AJ
31-Mar-22VJ
31-Mar-22RJ
31-Mar-22abcd
31-Mar-22AJ
31-Mar-22VJ
31-Mar-22RJ
31-Mar-22abcd
31-Mar-22AJ
30-Apr-22VJ
30-Apr-22RJ
30-Apr-22abcd
30-Apr-22AJ
30-Apr-22VJ
30-Apr-22RJ
30-Apr-22abcd
30-Apr-22AJ
30-Apr-22VJ
30-Apr-22RJ
30-Apr-22abcd
30-Apr-22AJ
30-Apr-22VJ
30-Apr-22RJ
30-Apr-22abcd
30-Apr-22AJ
30-Apr-22VJ
30-Apr-22RJ
30-Apr-22abcd
30-Apr-22AJ
30-Apr-22VJ
30-Apr-22RJ
30-Apr-22abcd
30-Apr-22AJ
30-Apr-22VJ
30-Apr-22RJ
30-Apr-22abcd
30-Apr-22AJ
30-Apr-22VJ
30-Apr-22RJ
30-Apr-22abcd
30-Apr-22AJ
30-Apr-22VJ
30-Apr-22RJ

 

Attrition Table

Employee NameSeparation Date
CJ2-Jan-22
EK3-Feb-22
LK4-Mar-22
OK5-Mar-22
DK6-Feb-22
TO7-Jan-22
RO8-Apr-22
EP9-Apr-22
LP10-Mar-22
RP11-Feb-22
AK12-Jan-22
TK13-Apr-22
LM14-Apr-22

 

Output Required

MonthHCAttritionMonthly AttritionMonthAnnualized Attrition %
Jan29310%1124%
Feb3439%2115%
Mar3838%3108%
Apr34412%4116%

 

Formula Used for Annualized Attrition

=(SUM(D$2:D2*12)/E2)

Message 9 of 9
815 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All