Forum Discussion

PHEstaciMa1's avatar
PHEstaciMa1
Icon for Helper II rankHelper II
4 years ago
Solved

How to calculate latest estimate using DAX

Hi All, I need help in putting a correct DAX on calculating latest estimate of a frequency rate.  Here's the formula I crafted in EXCEL.      Thanks in advance!
Need Help
Show and Tell
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, PHEstaciMa1 

    Try formulas as below:

    calculated column:

    Manhours2 = 
VAR value1 =
    AVERAGEX (
        FILTER ( 'Table', 'Table'[Manhours] <> BLANK () ),
        'Table'[Manhours]
    )
RETURN
    IF ( ISBLANK ( 'Table'[Manhours] ), value1, 'Table'[Manhours] )

    measure:

    M_Frequency Rate =
DIVIDE (
    SUM ( 'Table'[Count of Incidents] ) * 1000000,
    SUM ( 'Table'[Manhours2] )
)

    Best Regards,
    Community Support Team _ Eason

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi, PHEstaciMa1 

Try formulas as below:

calculated column:

Manhours2 = 
VAR value1 =
    AVERAGEX (
        FILTER ( 'Table', 'Table'[Manhours] <> BLANK () ),
        'Table'[Manhours]
    )
RETURN
    IF ( ISBLANK ( 'Table'[Manhours] ), value1, 'Table'[Manhours] )

measure:

M_Frequency Rate =
DIVIDE (
    SUM ( 'Table'[Count of Incidents] ) * 1000000,
    SUM ( 'Table'[Manhours2] )
)

Best Regards,
Community Support Team _ Eason

PHEstaciMa1's avatar
PHEstaciMa1
Icon for Helper II rankHelper II
4 years ago

Hi v-easonf-msft adding the creation of calendar table and the initial solution you provided, it solves my issue. thank you very much!