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

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

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

  • 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