Golf Stats Analysis - Countif and other such formulas in PowerBI

[Anonymous]

Hi

I am hoping to conduct some analysis on my golf game. I am somewhat new to PowerBI, and much more comfortable working with excel formulas.

I am collecting data from a MS Form which is then linked to PowerBI via PowerAutomate, if that gives you any insight into the format of the data. Currently the dummy data only accounts for Holes 1 & 2, eventually that will be extended out to the full 18 holes – I will make the changes to the formulas as appropriate then.

Attached is a excel screenshot of roughly the data analysis I would like to achieve (look below). The cells in green I have figured out however I am stuck on the remainder.

I am happy to share the pbix file as required (I dont know how here). This is the excel download of the MS FORM data.

Golf Stats - Excel spreadsheet. 

If anyone could help with any parts of this it would be appreciated. 

Regards

1. Fairways hit with each club type (driver, 3 wood etc). i.e. If the DRIVER was used, did it hit the fairway – example shows 11 times used, 7 fairways hit or 64% success rate. In excel I would normally go down the route of a COUNTIFS formula.
   1. I was using last N on filters to get the most recent round. Therefore with the filter removed I expect I should be able to get historical figures.
2. Greens in Regulation (GIR). The data received will show a “yes” or “No” for each hole if a GIR was successful or not. I have a formula working for that one as shown. I want to know my conversion rate of a FAIRWAY drive into a GIR success. I expect this will be a similar formula to the above (i.e. a COUNTIF). If HOLE 1 = FAIRWAY and GIR then count 1. If HOLE 2 = FAIRWAY and NOT GIR then count 0. 

# of GIR =

var _rows = {[Hole 1 - Green in Regulation], [Hole 2 - Green in Regulation]}

var _count =

    COUNTROWS( FILTER( _rows, [Value] = "No" ) )

RETURN

    IF( _count >0, _count, 0 )​

3. Putting – The data will have a total of 4 columns for each hole, if I have 2 putts on that hole, two columns will have data, 3 putts 3 columns and so on. The data collected is a distance or number type of data, 0.5, 0.9, 4.2 etc. I did read somewhere that an additional table would be required to set the desired ranges “putting ranges”. Not sure if that’s correct.

1. How many putts attempted at distance range? Using the numbers above, the answer for Putts 0 – 1m range would be 2 (0.5 & 0.9). How do lookup all putts for each hole and count the number of times an attempt was made at the distance range?
2. Putts made at distance. (This one is way to hard for me!). NOTE: If column Hole 2 – Putt 3 (Distance) does not contain a value, then the putt was made on putt 2 (as shown below).

1. Either, requires looking for the column “Hole 1 – Total Putts”, say equals 2, then find the corresponding column, “Hole 1 – Putt 2 (Distance)”, and if within the range 0 – 1m, then count 1. Then repeat for each hole. OR
2.       Find the maximum of “Hole 1 – Putt ? (Distance)” columns that contains data. And determine if value fits within range.      Example below shows 2 rounds of golf. If the range to be calculated was 0 – 2m distance, then the count for round 1 would be 0, and round 2 would be 1.      

1. % Today – should be an easy calculation of “putts made @distance” / “putt attempts@ distance).

1. Approach Shots

1. Count of Shots at Distance – should be similar to putting distances?? How many shots were taken from within the range specified?
2. Proximity to Hole (average distance today). This is the result of the shot taken.
   1.        If the data was as follows. Then the answer for the range 126 – 150m would be 7.5m (10 + 5 /2 = 7.5).

Hole 1 - Approach Shot (dist)	Hole 1 - Approach Shot (prox to hole)	Hole 2 - Approach Shot (dist)	Hole 2 - Approach Shot (prox to hole)
135	10	145	5

1. Prox to Hole (average distance history).
   1.       Across all rounds from a certain distance range, what is the average distance from the hole after the shot is taken?
2. Min and Max (Distance)
   1.       Of all the shots from the distance range on that day, what was the closest/furthest from the hole (result after approach shot made).

DESIRED DATA OUTPUTS