Forum Discussion
IanT76
6 years agoHelper I
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 Fir...
- 6 years ago
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.
amitchandak
6 years agoSuper User
IanT76 ,Can you share sample data and sample output.
IanT76
6 years agoHelper I
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 |