Daily DAX : Day 197 NORM.INV

 The NORM.INV function in Power BI DAX (Data Analysis Expressions) calculates the inverse of the cumulative distribution function (CDF) for a normal distribution. It returns the value ( x ) such that the probability of a normally distributed random variable being less than or equal to ( x ) is equal to a specified probability.

Syntax

dax

NORM.INV(probability, mean, standard_dev)

    probability: A value between 0 and 1 representing the probability associated with the normal distribution.

    mean: The arithmetic mean (average) of the distribution.

    standard_dev: The standard deviation of the distribution (must be positive).

Return Value

The function returns a value ( x ) (a number) from the normal distribution with the specified mean and standard deviation, where the cumulative probability 

P(X \leq x)

 equals the given probability.

How It Works

The NORM.INV function is based on the inverse of the cumulative normal distribution. It answers the question: "For a given probability, what is the value ( x ) in a normal distribution with a specific mean and standard deviation?"

For example:

    If you input a probability of 0.975, a mean of 100, and a standard deviation of 15, NORM.INV will return the value ( x ) where 97.5% of the distribution lies below ( x ).

Use Case

Scenario: A company wants to analyze customer satisfaction scores, which are normally distributed with a mean of 75 and a standard deviation of 10. They want to determine the score below which 95% of customers fall (i.e., the 95th percentile).

DAX Formula:

dax

CustomerScore_95th = NORM.INV(0.95, 75, 10)

Result: This returns approximately 91.45, meaning 95% of customers have a satisfaction score below 91.45.

Common Applications

    Statistical Analysis: Calculate thresholds or percentiles for normally distributed data (e.g., test scores, financial returns).

    Risk Management: Determine critical values for risk thresholds, such as Value-at-Risk (VaR) in finance.

    Quality Control: Identify cutoff points for acceptable product measurements in manufacturing.

    Forecasting: Estimate values for demand or sales based on probabilistic scenarios.

Notes

    Input Validation: Ensure probability is between 0 and 1, and standard_dev is positive. Invalid inputs will result in an error.

    Comparison to Excel: NORM.INV in DAX is similar to Excel’s NORM.INV function but is used within Power BI’s data modeling context.

    Limitations: Assumes a normal distribution, so it’s not suitable for non-normal data without transformation.

Example in Power BI

Suppose you have a table with sales data and want to calculate the sales value at the 90th percentile for a normally distributed dataset with a mean of 50,000 and a standard deviation of 5,000.

    Create a measure:

    dax

    Sales_90th_Percentile = NORM.INV(0.9, 50000, 5000)

    Use this measure in visuals to display the result (approximately 56,407).

This helps stakeholders understand the sales threshold below which 90% of sales fall.