The syntax for the NORM.INV function is as follows:
NORM.INV(probability, mean, standard_dev)
Here, probability is the probability value for which you want to find the z-score, mean is the arithmetic mean of the distribution, and standard_dev is the standard deviation of the distribution. All three arguments are required.
Suppose you have a dataset that contains the scores of a test, and you want to find the z-score for a student who scored 80 out of 100, assuming that the scores follow a normal distribution with a mean of 75 and a standard deviation of 10. You can use the NORM.INV function to do this.
The formula for finding the z-score using the NORM.INV function would be:
=NORM.INV((80-75)/10, 0, 1)
Here, (80-75)/10 calculates the standard score or z-score of 0.5. The mean value is 0 and the standard deviation is 1.
The output of this formula would be 0.8413, which represents the cumulative probability of the z-score being less than or equal to 0.5. In other words, there is an 84.13% chance of getting a score less than or equal to 80.
The NORM.INV function also has two optional parameters that you can use, as shown below:
NORM.INV(probability, mean, standard_dev, cumulative)
NORM.INV(probability, mean, standard_dev, [cumulative], [tails])
The cumulative parameter is a logical value that specifies whether you want to use the cumulative distribution function or the probability density function. By default, this parameter is set to TRUE, which means that the cumulative distribution function is used. If you set it to FALSE, the probability density function is used.
The tails parameter is a number that specifies the number of distribution tails to use. By default, this parameter is set to 2, which means that a two-tailed distribution is used. If you set it to 1, a one-tailed distribution is used.
The NORM.INV function in Power BI DAX is a powerful tool for calculating z-scores and probabilities in a normal distribution. By understanding its syntax and usage, you can use it to analyze data effectively and make informed decisions.