cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Measure displays values for excluded categories - what am I misunderstanding?

I have a table of average wage data from the bureau of labor statistics:

I want to create a measure that shows the weighted average of hourly wages for the "Production" population type only. I added a calculated column ("Weighted Amount" = hourly wage * employee count) to help with the weighted average calculation. I created a measure that divides the sum of weighted amount by the sum of employees:

``````AVG Production Hourly Wage =

CALCULATE(
(
SUM(FactBLSHourly[Weighted Amount]) / SUM(FactBLSHourly[BLS Employees (Thousands)])
),
FactBLSHourly[BLS Data Population Type] = "Production"
)``````

This works, but values also appear for the "Total" population type as well:

I expected there to be no values for the "Total" population type. The CALCULATE statement excludes them. Why are values showing up in the excluded category, and what am I misunderstanding about this measure?

Sample file with data available upon request.

1 ACCEPTED SOLUTION
Community Support

According to your description According to your description, you have doubts about the return result of the metric you wrote. Right?

Here is my explanation:

(1) First, based on your measure, we can write an equivalent measure as follows:

``````AVG Production Hourly Wage =

CALCULATE(

(

SUM('Test'[Weighted Amount]) / SUM('Test'[BLS Employees(Thousands)])

),

FILTER(ALL(Test[BLS Data Population Type]), 'Test'[BLS Data Population Type] = "Production")

)``````

(2) We can see that using a boolean expression below the CALCULATE() function will automatically clear the filter of the [BLS Data Population Type] column, thus displaying data in the visual that is inconsistent with the expected result.

(3) For the filter of CALCULATE(), we need to keep the filter of the column on the visual object, we can add the KEEPFILTERS() function outside the filter, the result is as follows:

``````AVG Production Hourly Wage =

CALCULATE(

(

SUM('Test'[Weighted Amount]) / SUM('Test'[BLS Employees(Thousands)])

),

KEEPFILTERS('Test'[BLS Data Population Type] = "Production")

)``````

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

5 REPLIES 5
Community Support

According to your description According to your description, you have doubts about the return result of the metric you wrote. Right?

Here is my explanation:

(1) First, based on your measure, we can write an equivalent measure as follows:

``````AVG Production Hourly Wage =

CALCULATE(

(

SUM('Test'[Weighted Amount]) / SUM('Test'[BLS Employees(Thousands)])

),

FILTER(ALL(Test[BLS Data Population Type]), 'Test'[BLS Data Population Type] = "Production")

)``````

(2) We can see that using a boolean expression below the CALCULATE() function will automatically clear the filter of the [BLS Data Population Type] column, thus displaying data in the visual that is inconsistent with the expected result.

(3) For the filter of CALCULATE(), we need to keep the filter of the column on the visual object, we can add the KEEPFILTERS() function outside the filter, the result is as follows:

``````AVG Production Hourly Wage =

CALCULATE(

(

SUM('Test'[Weighted Amount]) / SUM('Test'[BLS Employees(Thousands)])

),

KEEPFILTERS('Test'[BLS Data Population Type] = "Production")

)``````

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helper II

Thank you! This is the first time I have encountered the KEEPFILTERS function. I want to make sure I understand. By default, a measure made with the CALCULATE function will disregard any segmenters that are used as filters. Because I used the column [BLS Data Population Type] as a filter in a CALCULATE function, the resulting measure did not respect [BLS Data Population Type] in the filter context of visualizations (such as a table). The solution to this was to wrap the filter in the "KEEPFILTER" function, which forces the measure to respect this column again. Is this correct?

Community Support

Your understanding is correct, you can refer to the documentation about KEEPFILTERS(): https://docs.microsoft.com/en-us/dax/keepfilters-function-dax

Second, if you don't want to use the KEEPFILTERS() function you can also use the following DAX:

``````AVG Production Hourly Wage =
CALCULATE (
( SUM ( 'Test'[Weighted Amount] ) / SUM ( 'Test'[BLS Employees(Thousands)] ) ),
FILTER ( 'Test', 'Test'[BLS Data Population Type] = "Production" )
)
``````

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helper II

Thank you!

Helper II

Thank you! This is the first time I have encountered the KEEPFILTERS function. I want to make sure I understand. By default, a measure made with the CALCULATE function will disregard any segmenters that are used as filters. Because I used the column [BLS Data Population Type] as a filter in a CALCULATE function, the resulting measure did not respect [BLS Data Population Type] in the filter context of visualizations (such as a table). The solution to this was to wrap the filter in the "KEEPFILTER" function, which forces the measure to respect this column again. Is this correct?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.