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

[sean_cochran]

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.

[v-yueyunzh-msft · Accepted solution]

Hi, @sean_cochran 

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

[sean_cochran]

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?

[v-yueyunzh-msft]

Hi, @sean_cochran 

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

[sean_cochran]

Thank you!

[sean_cochran]

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?