Forum Discussion

AjayReddy's avatar
AjayReddy
Regular Visitor
4 years ago

Unable to complete %Formula

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   ...
Need Help
AjayReddy's avatar
AjayReddy
Regular Visitor
4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • AjayReddy's avatar
    AjayReddy
    Regular Visitor
    4 years ago

    Hey,

     

    Did you miss the formula cause I do not see it

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

     

    Thanks,

    AJ

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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

      • AjayReddy's avatar
        AjayReddy
        Regular Visitor
        4 years ago

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

        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)