Question

A Power BI model has a Sales fact table and a Product dimension with a Category column. You write a measure to show only sales that exceed the overall average, but when placed in a matrix broken down by Category, the measure incorrectly compares each category to itself rather than to all categories. The current definition is: HighValueSales = IF([Total Sales] > CALCULATE(AVERAGE(Sales[Amount])), [Total Sales]). What is the correct way to compare each category's total against the average across ALL categories?

Accepted Answer

Use CALCULATE(AVERAGE(Sales[Amount]), ALL(Product[Category])) to remove the Category filter when computing the benchmark average

Answer

Use CALCULATE(AVERAGE(Sales[Amount]), KEEPFILTERS(Product[Category])) to preserve the current filter context

Answer

Use CALCULATE(AVERAGE(Sales[Amount]), VALUES(Product[Category])) to iterate over each visible category

Answer

Use CALCULATE(AVERAGE(Sales[Amount]), FILTER(Product, Product[Category] = SELECTEDVALUE(Product[Category]))) to lock the current category