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, and accuratly calc the totals? I have read and tried a few options with no luck.  Thanks in andvance! 

 

Retention % =
VAR _RenentionVar =
([Hires] + [Opening Headcount])
Return
CALCULATE(DIVIDE([Terms],_RenentionVar)-1)*-1
 
Retention % Net =
VAR _RenentionVar =
([Hires] + [Opening Headcount])
Return
DIVIDE([Terms],_RenentionVar,0)
 
Retention % good x =
VAR _Retention =
([Hires] + [Opening Headcount])
Return
SUMX(
SUMMARIZE(DimCalendar,
DimCalendar[Date].[Month],
DimCalendar[Date].[Quarter],
DimCalendar[Date].[Year], "_1",
DIVIDE([Terms], _Retention)),[_1])

 

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 %])

1 Reply

  • 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 %])