Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

KPI Formula Power Pivot

Hello,

 

I am very new in this platform, and I want to learn more. I am currently building a DAX in which I'll be able to produce this formula: Divide(# of Red to Yellow, Red)

 

How do I produce this in DAX. I can't share the data, but I'll try to elaborate the situation further. 

 

It's an EWS formula. Red means people will be resigning within 30 days and yellow means within 60 days. It's an RYG tagging wherein we identify people that will be separating from the company. In the formula, the Red should be the count from the minimum selected of date while the yellow is the count of the maximum selected date.

 

So technically the formula should be: # of Employees converted from Red to Yellow / Count of Red from the minimum selected date. It's like Divide(Red to yellow, Red)

 

However, I'm getting a hard time getting the count of employees that were converted from Red to Yellow.

Thank you in advance! I'm not sure which dax syntax I should use at this point as I just started using powerpivot/bi's dax this week.

Help Request

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    sensitive and unsharable data is normal. try to distill the logic and use sample data with dummy name and value to raise the questions. it helps those who can help you. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    This is what I was trying to show in pivot. However, this is the formula I created on this.

     

    =VAR RTY =

    IF(AND(ISBLANK([MinRed]),ISBLANK([MaxYellow])),0,

    IF(AND(ISBLANK([MinRed]),[MaxYellow]>0),0,

    IF(AND([MinRed]>0,ISBLANK([MaxYellow])),0,

    IF(AND([MinRed]>0,[MaxYellow]>0),[MinRed]-[MaxYellow],0))))

     

    return IF(AND([MinRed]>0,[MaxYellow]>0),

    IF(RTY=0,1,RTY/[MinRed]),"")


    Whenever there's a value of MinRed to MaxYellow, it still computes regardless wherever it came from especially in manager level. So it kinda breaks the accuracy.

    Example below, there should only be one Red to Yellow

     

     

    However, after collapsing the rows. This is what I see. It calculates even the employees that are not qualified for Red to Yellow (Partial Transition Save Rate)

     

     

     

    This is the raw data:

     

    PUIDUIDWEIDEIDEmployeeNameSupervisorManagerSubLOBLOBDetailsWeekBeginningForecastedDateOfAttritionEWSCategoryFormulatedRCL1RCL2RCL3RTORelated?(Yes/No)EmployeeRetentionCreatedCreatedByChangeLogV2EWSCategorySaved?(Y/N)Status(Database)SeparationDateInterventionLevelInterventionDate
    Green3691374486904-Nov1326369137Employee1Supervisor1Manager1GE EBI   Green     2022-10-20T11:18:21ZCreator1   Active   
    Green3691374487611-Nov1326369137Employee1Supervisor1Manager1GE EBI   Red     2022-10-20T11:18:21ZCreator1   Active   
    Red3691374488318-Nov1326369137Employee1Supervisor1Manager1GE EBI   Yellow     2022-10-20T11:18:21ZCreator1   Active   
    Yellow3691374489025-Nov1326369137Employee1Supervisor1Manager1GE EBI   Yellow     2022-10-20T11:18:21ZCreator1   Active   
    Green3691374486904-Nov1326369137Employee2Supervisor1Manager1GE EBI   Red     2022-10-20T11:18:21ZCreator1   Active   
    Green3691374487611-Nov1326369137Employee2Supervisor1Manager1GE EBI   Red     2022-10-20T11:18:21ZCreator1   Active   
    Red3691374488318-Nov1326369137Employee2Supervisor1Manager1GE EBI   Red     2022-10-20T11:18:21ZCreator1   Active   
    Yellow3691374489025-Nov1326369137Employee2Supervisor1Manager1GE EBI   Red     2022-10-20T11:18:21ZCreator1   Active   
    Green3691374486904-Nov1326369137Employee3Supervisor1Manager1GE EBI   Yellow     2022-10-20T11:18:21ZCreator1   Active   
    Green3691374487611-Nov1326369137Employee3Supervisor1Manager1GE EBI   Yellow     2022-10-20T11:18:21ZCreator1   Active   
    Red3691374488318-Nov1326369137Employee3Supervisor1Manager1GE EBI   Yellow     2022-10-20T11:18:21ZCreator1   Active   
    Yellow3691374489025-Nov1326369137Employee3Supervisor1Manager1GE EBI   Yellow     2022-10-20T11:18:21ZCreator1   Active   
    Green3691374486904-Nov1326369137Employee4Supervisor1Manager1GE EBI   Red     2022-10-20T11:18:21ZCreator1   Active   
    Green3691374487611-Nov1326369137Employee4Supervisor1Manager1GE EBI   Red     2022-10-20T11:18:21ZCreator1   Active   
    Red3691374488318-Nov1326369137Employee4Supervisor1Manager1GE EBI   Red     2022-10-20T11:18:21ZCreator1   Active   
    Yellow3691374489025-Nov1326369137Employee4Supervisor1Manager1GE EBI   Red     2022-10-20T11:18:21ZCreator1   Active