KPI Formula Power Pivot

[vanhuxley24]

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.

[vanhuxley24]

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:

PUID

UID

WE

ID

EID

EmployeeName

Supervisor

Manager

SubLOB

LOB

Details

WeekBeginning

ForecastedDateOfAttrition

EWSCategoryFormulated

RCL1

RCL2

RCL3

RTORelated?(Yes/No)

EmployeeRetention

Created

CreatedBy

ChangeLogV2

EWSCategory

Saved?(Y/N)

Status(Database)

SeparationDate

InterventionLevel

InterventionDate

Green

36913744869

04-Nov

1326

369137

Employee1

Supervisor1

Manager1

GE E

BI

Green

2022-10-20T11:18:21Z

Creator1

Active

Green

36913744876

11-Nov

1326

369137

Employee1

Supervisor1

Manager1

GE E

BI

Red

2022-10-20T11:18:21Z

Creator1

Active

Red

36913744883

18-Nov

1326

369137

Employee1

Supervisor1

Manager1

GE E

BI

Yellow

2022-10-20T11:18:21Z

Creator1

Active

Yellow

36913744890

25-Nov

1326

369137

Employee1

Supervisor1

Manager1

GE E

BI

Yellow

2022-10-20T11:18:21Z

Creator1

Active

Green

36913744869

04-Nov

1326

369137

Employee2

Supervisor1

Manager1

GE E

BI

Red

2022-10-20T11:18:21Z

Creator1

Active

Green

36913744876

11-Nov

1326

369137

Employee2

Supervisor1

Manager1

GE E

BI

Red

2022-10-20T11:18:21Z

Creator1

Active

Red

36913744883

18-Nov

1326

369137

Employee2

Supervisor1

Manager1

GE E

BI

Red

2022-10-20T11:18:21Z

Creator1

Active

Yellow

36913744890

25-Nov

1326

369137

Employee2

Supervisor1

Manager1

GE E

BI

Red

2022-10-20T11:18:21Z

Creator1

Active

Green

36913744869

04-Nov

1326

369137

Employee3

Supervisor1

Manager1

GE E

BI

Yellow

2022-10-20T11:18:21Z

Creator1

Active

Green

36913744876

11-Nov

1326

369137

Employee3

Supervisor1

Manager1

GE E

BI

Yellow

2022-10-20T11:18:21Z

Creator1

Active

Red

36913744883

18-Nov

1326

369137

Employee3

Supervisor1

Manager1

GE E

BI

Yellow

2022-10-20T11:18:21Z

Creator1

Active

Yellow

36913744890

25-Nov

1326

369137

Employee3

Supervisor1

Manager1

GE E

BI

Yellow

2022-10-20T11:18:21Z

Creator1

Active

Green

36913744869

04-Nov

1326

369137

Employee4

Supervisor1

Manager1

GE E

BI

Red

2022-10-20T11:18:21Z

Creator1

Active

Green

36913744876

11-Nov

1326

369137

Employee4

Supervisor1

Manager1

GE E

BI

Red

2022-10-20T11:18:21Z

Creator1

Active

Red

36913744883

18-Nov

1326

369137

Employee4

Supervisor1

Manager1

GE E

BI

Red

2022-10-20T11:18:21Z

Creator1

Active

Yellow

36913744890

25-Nov

1326

369137

Employee4

Supervisor1

Manager1

GE E

BI

Red

2022-10-20T11:18:21Z

Creator1

Active

[FreemanZ]

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.