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

 

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

Need Help

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    AjayReddy , Try like

     

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

     

    or


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

    • 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