Calculate frequency using pre-determined formulae

[IanT76]

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!

[v-alq-msft · Accepted solution]

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.

[IanT76]

Fantastic Allan, thank you!!!

[amitchandak]

@IanT76 ,Can you share sample data and sample output.

[IanT76]

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