Forum Discussion

Fro88er's avatar
Fro88er
Helper IV
5 years ago
Solved

Remove 100% from table/matrix

I am trying to calculate the [Retention %] ((Hired + Opening Headcount)/ Terms).  When I try and flip the % to a %of 100 it blows-out the future dates with 100.0%.  Is there a way I can stop this, an...
Need Help
  • Fro88er's avatar
    Fro88er
    5 years ago

    I worked through my own question.

     

    Retention % =
    VAR _HasOneValue =
    IF(HASONEVALUE('FactMonth End HC'[Report Pulled On]), 1, BLANK())
    Return
    CALCULATE([Retention % Net] - _HasOneValue) * -1

    Retention % FINAL =
    AVERAGEX(
    SUMMARIZE(DimCalendar,
    DimCalendar[Date].[Month],
    DimCalendar[Date].[Quarter],
    DimCalendar[Date].[Year]),
    [Retention %])
Fro88er's avatar
Fro88er
Helper IV
5 years ago

I worked through my own question.

 

Retention % =
VAR _HasOneValue =
IF(HASONEVALUE('FactMonth End HC'[Report Pulled On]), 1, BLANK())
Return
CALCULATE([Retention % Net] - _HasOneValue) * -1

Retention % FINAL =
AVERAGEX(
SUMMARIZE(DimCalendar,
DimCalendar[Date].[Month],
DimCalendar[Date].[Quarter],
DimCalendar[Date].[Year]),
[Retention %])