Basically, the device has a MAC address programmed in from the factory that never changes. When the device connects to a network, this MAC address could be recorded, and then used to track the location of that device as it connects to other networks owned by the same company. So, Apple implemented a feature that lets you generate a new, random MAC address to use when connecting to a network. The "Fixed" version of this generates one new MAC address the first time your device connects to a particular network, and then keeps it around to use every time you connect to that network again (each network gets their own address). This is handy for things like hotels, convention centers, or hospitals where you have to authenticate through a captive portal, since using the same address lets you avoid having to reauthenticate repeatedly. But, it does mean that network owner can still theoretically track your device. So, this improves privacy, but it's not perfect.
This is where the "Rotate" option comes in. For that one, the device generates a new random MAC address every time it connects. That stops tracking, but can be inconvenient for captive portal networks.
Notice how both of the above options are really focused on increasing privacy when connecting to public or facility WiFi networks? In your home network, you are in control, and you aren't going to be tracking your own devices. So, privacy isn't an issue – that's why "Off" is safe to use for networks you own and control. That uses the factory MAC address and no random MAC addresses at all.
Lots of words, but I wanted to make sure you understood the context of the short answer: "Off" is simpler, and quite safe to use for networks you trust while "Fixed" and "Rotating" improve privacy when connecting to networks you don't trust.
