Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to Solution.
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
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
Hi @v-easonf-msft adding the creation of calendar table and the initial solution you provided, it solves my issue. thank you very much!
Hi @v-easonf-msft I tried your but somehow the remaining months of 2021 which has no data does not appear in my table after I unpivot the column. You may check the file I uploaded here in this link --> How to calculate latest estimate using DAX
Hi, @PHEstaciMa1
Not fully sure what you mean.
Please check the table visual I created.
Best Regards,
Community Support Team _ Eason
I see that you have create a calendar table for a period of 2018 to end of 2021, I guess that will be fine. Let me try it again and let you know ASAP.