Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
sean_cochran
Helper II
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:

sean_cochran_1-1663101130493.png

 

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:

sean_cochran_3-1663101372704.png

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
v-yueyunzh-msft
Community Support
Community Support

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.

vyueyunzhmsft_0-1663138823380.png

 

(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")

)

 

vyueyunzhmsft_1-1663138823384.png

 

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

View solution in original post

5 REPLIES 5
v-yueyunzh-msft
Community Support
Community Support

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.

vyueyunzhmsft_0-1663138823380.png

 

(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")

)

 

vyueyunzhmsft_1-1663138823384.png

 

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

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?

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

Thank you!

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?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors