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
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

      • 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