cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Formula help

Hello Community, I hope you are well...

I tell you my problem in summary form:

1. I need to generate a simple calculation. I try to do it but it does not calculate well, I have this example case:

# The company FLSMIDTH has 3 accidents with injured people and 902,343 hours worked, so they ask me to calculate an accident rate, which is calculated as follows 3 (accidents)* 1000000 (average hours per year) / 902343 (hours worked. As a result it gives 3.32

# to extract the hours worked I have this database (I attach only a part, since there are many companies):

Jos_Valds_0-1634174846025.png

# when performing the calculation with the following formulas:

  • (Formula to extract the hours) HH_EECC = CALCULATE(COUNTROWS('BD_Horas EECC'),'BD_Horas EECC'[Year]="2021")
  • (formulate to perform the calculation) IF_EECC = ([CTP]+[FATAL])*1000000/[HH_EECC]
    • CTP Refers to Accidents with Injury
    • Fatal refers to accidents with deceased

gives me an erroneous calculation

Jos_Valds_1-1634174996927.png

Thank you

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

and change the formula as you recommended HH_EECC = sum('BD_Horas EECC'[HH]),however when making the calculation IF_EECC = ([CTP]+[FATAL])*1000000/[HH_EECC] still gives me an erroneous result (0.35) remember that the value that I should deliver is 3.32, considering the data delivered previously (3 * 1000000 / hh)

Syndicate_Admin
Administrator
Administrator

Exactly, the HH columa contains the number of hours. What I need is that the formula performs the calculation correctly, since when making this formula: IF_EECC = ([CTP]+[FATAL])*1000000/[HH_EECC], where I add the accidents with injury (CTP + FATAL) I multiply it by 1,000,000 (this is a factor of the formula that should not be eliminated) and by dividing by the hh (hours), should result in 3.32, considering the data provided above (3*1000000/hh)

AllisonKennedy
Super User
Super User

@Syndicate_Admin @José_Valdés 

 

Does your HH column contain the number of Hours? Do you need to SUM that? You're doing a COUNTROWS. 

 

Try changing your Hours measure to: 

HH_EECC = SUM('BD_Horas EECC'[HH])

 

Note I removed the 2021 filter -I'd do that more dynamically using Slicers or other method, rather than hard coding it into the DAX.

 

 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

and change the formula as you recommended HH_EECC = sum('BD_Horas EECC'[HH]),however when making the calculation IF_EECC = ([CTP]+[FATAL])*1000000/[HH_EECC] still gives me an erroneous result (0.35) remember that the value that I should deliver is 3.32, considering the data delivered previously (3 * 1000000 / hh)

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!