Forum Discussion
Calculate frequency using pre-determined formulae
I'm looking to display a lot of historic safety statistics & frequencies, using our industry's pre-determined calculations, with one example being First Aid Case frequency (FACf);
- FACf = No. of First Aid Cases per 200,000 Hours Worked.
I have all hours worked data, incident type numbers, dates etc, and have succesfuly managed to graph actual numbers of Hours Worked and First Aid cases (per month and running total for Year to Date), and I have built the FACf formula in a new column, FACfCalc;
- FACfCalc = ((200000*Table_query__3[FAC's])/Table_query__3[Hours Worked])
But the formula is not returning the correct results...looking at January in below example, 5 FAC's with 430,000 Hours Worked in the month should return an FACf of 2.32 (200,000 * 5) / 430,000) but it's not working....
I've tried both possible variations of the formula (200k / Hours * FAC's and 200k * FAC's / Hours) but no luck! Any help gratefully appreciated!
Hi, IanT76
I'd like to suggest you create a measure as below.
Result = var _month = SELECTEDVALUE('Table'[Month]) return ( 200000* CALCULATE( SUM('Table'[FAC's]), 'Table'[Month] = _month ) )/ CALCULATE( SUM('Table'[Hours Worked]), 'Table'[Month] = _month )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi, IanT76
I'd like to suggest you create a measure as below.
Result = var _month = SELECTEDVALUE('Table'[Month]) return ( 200000* CALCULATE( SUM('Table'[FAC's]), 'Table'[Month] = _month ) )/ CALCULATE( SUM('Table'[Hours Worked]), 'Table'[Month] = _month )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Fantastic Allan, thank you!!!
IanT76 ,Can you share sample data and sample output.
Hi Amit,
Sample data is below and desired output is as per graphs in original post; the top 2 graphs are working correctly but the bottom one (showing FACf) is not....
Country Year Month Hours Worked FAC's FACf - calculated manually as
((200,000 / Hours Worked ) * FAC's)
A 2015 January 132488 0 0.00 A 2015 February 154416 1 1.30 A 2015 March 175820 0 0.00 A 2015 April 176312 1 1.13 A 2015 May 181056 1 1.10 A 2015 June 175952 0 0.00 A 2015 July 173404 0 0.00 A 2015 August 173592 0 0.00 A 2015 September 147192 0 0.00 A 2015 October 161456 0 0.00 A 2015 November 147744 0 0.00 A 2015 December 147388 0 0.00 D 2015 January 11408 0 0.00 D 2015 February 10736 0 0.00 D 2015 March 11408 0 0.00 D 2015 April 10736 0 0.00 D 2015 May 10920 0 0.00 D 2015 June 11264 0 0.00 D 2015 July 11776 0 0.00 D 2015 August 11088 0 0.00 D 2015 September 11440 0 0.00 D 2015 October 11440 0 0.00 D 2015 November 11792 0 0.00 D 2015 December 12880 0 0.00 G 2015 January 108576 4 7.37 G 2015 February 98656 2 4.05 G 2015 March 108944 5 9.18 G 2015 April 105376 1 1.90 G 2015 May 106728 0 0.00 G 2015 June 69384 2 5.77 G 2015 July 71560 3 8.38 G 2015 August 55908 1 3.58 G 2015 September 54080 0 0.00 G 2015 October 46612 2 8.58 G 2015 November 45256 1 4.42 G 2015 December 42356 1 4.72 K 2015 January 35088 0 0.00 K 2015 February 38208 0 0.00 K 2015 March 49416 0 0.00 K 2015 April 43800 0 0.00 K 2015 May 56748 0 0.00 K 2015 June 60360 0 0.00 K 2015 July 50716 0 0.00 K 2015 August 71796 0 0.00 K 2015 September 56936 0 0.00 K 2015 October 49352 0 0.00 K 2015 November 50816 0 0.00 K 2015 December 38440 0 0.00 M 2015 January 138252 1 1.45 M 2015 February 126688 0 0.00 M 2015 March 142532 0 0.00 M 2015 April 134920 1 1.48 M 2015 May 152856 0 0.00 M 2015 June 151128 0 0.00 M 2015 July 155928 1 1.28 M 2015 August 169236 0 0.00 M 2015 September 166272 0 0.00 M 2015 October 173536 1 1.15 M 2015 November 176176 0 0.00 M 2015 December 179936 0 0.00 MONTH TOTAL 2015 January 425812 5 2.35 MONTH TOTAL 2015 February 428704 3 1.40 MONTH TOTAL 2015 March 488120 5 2.05 MONTH TOTAL 2015 April 471144 3 1.27 MONTH TOTAL 2015 May 508308 1 0.39 MONTH TOTAL 2015 June 468088 2 0.85 MONTH TOTAL 2015 July 463384 4 1.73 MONTH TOTAL 2015 August 481620 1 0.42 MONTH TOTAL 2015 September 435920 0 0.00 MONTH TOTAL 2015 October 442396 3 1.36 MONTH TOTAL 2015 November 431784 1 0.46 MONTH TOTAL 2015 December 421000 1 0.48 YEAR TOTAL 2015 5466280 29 1.06
