Forum Discussion

JP8991's avatar
JP8991
Kudo Commander
5 years ago
Solved

VAR Measure Not Working

Seperating all of these measures out it works fine however I cannot seem to get them working in a single measure.

Is there anything I am missing?

 

Centre Turnover (NEW) =

VAR Rolling = DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH)

VAR CentreHeadcount = CALCULATE(COUNT('Employee Data'[ID Number]), 'Employee Data'[Business Area] = "Centre", 'Employee Data'[Status] <> "T", 'Employee Data'[Employment Type] <> "Casual")

VAR AverageHeadcount = AVERAGEX(Rolling, CentreHeadcount)

VAR CentreTerminations = CALCULATE(DISTINCTCOUNT('Employee Data'[ID Number]), 'Employee Data'[Business Area] = "Centre", 'Employee Data'[Status] = "T", 'Employee Data'[Employment Type] <> "Casual",  USERELATIONSHIP('Calendar'[Date], 'Employee Data'[Term Date]))

VAR CentreTerminations12MTD = CALCULATE(CentreTerminations, Rolling)

 

RETURN

 

DIVIDE(CentreTerminations12MTD, AverageHeadcount, 0)

 

Need Help
  • JP8991's avatar
    JP8991
    5 years ago

    Ok, so I managed to figure this out myself.

    I removed the reference to variables within the variables themselves, makes for a neater formula and gave me the desired result!

    Centre Turnover (NEW) =
    VAR AverageHeadcount = AVERAGEX(DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH),AVERAGEX(DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH), CALCULATE(COUNT('Employee Data'[ID Number]), 'Employee Data'[Business Area] = "Centre", 'Employee Data'[Status] <> "T", 'Employee Data'[Employment Type] <> "Casual")))

    VAR CentreTerminations12MTD = CALCULATE(CALCULATE(DISTINCTCOUNT('Employee Data'[ID Number]), 'Employee Data'[Business Area] = "Centre", 'Employee Data'[Status] = "T", 'Employee Data'[Employment Type] <> "Casual", 'Employee Data'[Term Type] <> "N/A", USERELATIONSHIP('Calendar'[Date], 'Employee Data'[Term Date])), DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH))

    RETURN

    DIVIDE(CentreTerminations12MTD, AverageHeadcount, 0)

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    JP8991 , Try to change Avg head count like the below and check

     

    VAR AverageHeadcount = AVERAGEX(values('Calendar'[Month Year]),calculate( CentreHeadcount,Rolling) )

    VAR AverageHeadcount = calculate( AVERAGEX(values('Calendar'[Month Year]),( CentreHeadcount)),Rolling)

  • JP8991's avatar
    JP8991
    Kudo Commander
    5 years ago

    Ok, so I managed to figure this out myself.

    I removed the reference to variables within the variables themselves, makes for a neater formula and gave me the desired result!

    Centre Turnover (NEW) =
    VAR AverageHeadcount = AVERAGEX(DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH),AVERAGEX(DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH), CALCULATE(COUNT('Employee Data'[ID Number]), 'Employee Data'[Business Area] = "Centre", 'Employee Data'[Status] <> "T", 'Employee Data'[Employment Type] <> "Casual")))

    VAR CentreTerminations12MTD = CALCULATE(CALCULATE(DISTINCTCOUNT('Employee Data'[ID Number]), 'Employee Data'[Business Area] = "Centre", 'Employee Data'[Status] = "T", 'Employee Data'[Employment Type] <> "Casual", 'Employee Data'[Term Type] <> "N/A", USERELATIONSHIP('Calendar'[Date], 'Employee Data'[Term Date])), DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH))

    RETURN

    DIVIDE(CentreTerminations12MTD, AverageHeadcount, 0)